VBA to replace row numbers in a range with variables e.g Range("$A$RowFirst:$B$RowLast")

[NotBillGates]

Hi
I have 35 rows and 2 columns A and B
I want to filter by TopTen values in column B in rows 8 to 25

I have used the following to find the first and last row number (thanks to WBD)

Dim rowNumberFirst As Double
On Error Resume Next
Err.Clear
rowNumberFirst = WorksheetFunction.Match("FirstApples", ActiveSheet.Range("$A:$A"), 0)
If Err.Number <> 1004 Then
End If

This sets rowNumberFirst to 8

Dim rowNumberLast As Double
On Error Resume Next
Err.Clear
rowNumberFirst = WorksheetFunction.Match("LastApples", ActiveSheet.Range("$A:$A"), 0)
If Err.Number <> 1004 Then
End If

This sets rowNumberLast to 25

The following hard coding works
ActiveSheet.Range("$A$8:$B$25").AutoFilter Field:=2, Criteria1:="10", _
Operator:=xlTop10Items

but how do I replace the 8 with rowNumberFirst and 25 with rowNumberLast e.g.

ActiveSheet.Range("$A$rowNumberFirst:$B$2rowNumberLast).AutoFilter Field:=2, Criteria1:="10", _
Operator:=xlTop10Items

Any help greatly appreciated

Thanks

 

[Norie]

Try this.

ActiveSheet.Range("$A$" & rowNumberFirst & ":$B$" & rowNumberLast).AutoFilter Field:=2, Criteria1:="10", _
Operator:=xlTop10Items

 

[NotBillGates]

Thanks Norie
Works perfectly