我如何命名使用最后一行和最后一列VBA生成的动态范围?

Question

我有一个问题，我要找的是使一个代码是自适应的(唯一修改的是行数，而不是列)，我需要给它一个名字，因为我将使用列表作为一个列表范围在高级过滤器选项和列整数作为标准范围，这是我的代码，如果你建议别的东西我接受建议。

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("Sheet1")
Set StartCell = Range("A1")
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column.End(xlUp).Row)
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count.End(xlToLeft).Column)
sht.Range(StartCell, sht.Cells(LastRow, LastColumn).Select)

p。s:我是VBA新手

Answer 1

不需要StartCell，除非你要改变它，否则你只使用sht.Cells(1,1)。最后，您选择了一个范围。最好避免使用Activate和Select。如果你定义了范围，你可以复制、搜索等。

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Set sht = Worksheets("Sheet1")
LastRow = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row
LastColumn = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column
Dim Rng As Range
Set Rng = sht.Range(1, sht.Cells(LastRow, LastColumn))
Rng.AutoFilter, Field:=1, Criteria1:="New"