在 ActiveSheet.Range 中应用动态范围

Question

我运行了一个宏来使用指定的数据集粘贴、编辑、更改和拆分数据。

在一个新的数据集(更多数据(上，我的数据范围遇到了问题。

ActiveSheet.Range("$A$1:$T$299").AutoFilter Field:=6, Criteria1:= _
"=Site Reference A", Operator:=xlOr, Criteria2:= _
"=Site Reference A  Total"
Range("A1:T299").Select
Range("F160").Activate
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

我的数据范围是 $A$1：$T$299。我将如何使这种动态？

示例 -
数据集 A 有 200 列。
数据集 B 有 230 列。

Answer 1

有点长，但这个完整的代码是查找工作表中最后一行和最后一列的更安全的方法。

Dim Sht As Worksheet
Dim FiltRng As Range
Dim LastCol As Long
Dim LastRow As Long
Set Sht = ActiveSheet '<-- better not rely on ActiveSheet
LastRow = FindLastRow(Sht)
LastCol = FindLastCol(Sht)
With Sht
Set FiltRng = .Range(.Cells(1, 1), .Cells(LastRow, LastCol)) ' <-- set the filtered range dynamically
End With
With Sht
FiltRng.AutoFilter Field:=6, Criteria1:= _
"=Site Reference A", Operator:=xlOr, Criteria2:= _
"=Site Reference A  Total"
' rest of your code goes here
End With

'===

===============================================================

Function FindLastCol(Sht As Worksheet) As Long
' This Function finds the last col in a worksheet, and returns the column number
Dim LastCell As Range
With Sht
Set LastCell = .Cells.Find(What:="*", After:=.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
If Not LastCell Is Nothing Then
FindLastCol = LastCell.Column
Else
MsgBox "Error! worksheet is empty", vbCritical
End
End If
End With
End Function

'===

===============================================================

Function FindLastRow(Sht As Worksheet) As Long
' This Function finds the last row in a worksheet, and returns the row number
Dim LastCell As Range
With Sht
Set LastCell = .Cells.Find(What:="*", After:=.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
If Not LastCell Is Nothing Then
FindLastRow = LastCell.Row
Else
MsgBox "Error! worksheet is empty", vbCritical
End
End If
End With
End Function

Answer 2

如果 A 列中没有其他数据，则可以使用动态命名区域。 只需使用以下公式作为命名范围的范围，然后在代码中引用命名范围。 请记住，这仅在 A 列中没有其他数据时才有效。

=OFFSET($A$1,0,0,COUNTA($A:$A),1)