LastRow

[Seandobson2402]

Hi Guy,

Still getting to grips with VBA and I'm wondering if anyone can help me with the below code.

Instead of filtering to row 10841, I would like to locate the last row and use this. My data will get higher that 10841 rows and I don't want to have to keep increasing the number.

Private Sub FilterEmployees()

    ActiveSheet.Range("$A$1:$F$10841").AutoFilter Field:=4, Criteria1:=Array( _
        "Name 1", "Name 2", "Name 3", "Name 4"), Operator:= _
        xlFilterValues
        
End Sub

 

[My Aswer Is This]

I would do it this way:

Dim Lastrow As Long
Lastrow = Activsheet.Cells(Rows.Count, "F").End(xlUp).Row
ActiveSheet.Range ("A1:F" & Lastrow)

 

[Rick Rothstein]

As long as you will never have data in the filtered columns that should not be part of the filter, why not simply specify the entire column for you AutoFilter... that way you won't have to worry about the last row. Also, while I am not 100% sure, I would be willing to bet that AutoFilter automatically limits itself to the last row of data within the filtered range. For example, if you select Columns A:F and apply the AutoFilter, then execute this in the Immediate Window...

? ActiveSheet.AutoFilter.Range.Address

you will see the range appears to limit itself to the last row of data automatically.

 

[My Aswer Is This]

As long as you will never have data in the filtered columns that should not be part of the filter, why not simply specify the entire column for you AutoFilter... that way you won't have to worry about the last row. Also, while I am not 100% sure, I would be willing to bet that AutoFilter automatically limits itself to the last row of data within the filtered range. For example, if you select Columns A:F and apply the AutoFilter, then execute this in the Immediate Window...

? ActiveSheet.AutoFilter.Range.Address

you will see the range appears to limit itself to the last row of data automatically.

Rick would you show us how to do this using Vba. Without needing lastrow.

Show us the code needed

 

[Rick Rothstein]

Rick would you show us how to do this using Vba. Without needing lastrow.

Show us the code needed

ActiveSheet.Range("A:F").AutoFilter

Specify the optional arguments as needed.

 

[My Aswer Is This]

ActiveSheet.Range("A:F").AutoFilter

Specify the optional arguments as needed.

I thought the whole purpose of using lastrow was to keep the script from searching for values in all 1.5 million rows.

 

[Rick Rothstein]

I thought the whole purpose of using lastrow was to keep the script from searching for values in all 1.5 million rows.

I did say I wasn't 100% sure, but based on what AutoFilter tells me its Range is (based on what I posted in Message #3 ), I have to conclude that it limits itself automatically to the last row in the filtered columns. I could be wrong, but it does not seem so to me based on that.