Moving Down in Filtered Area VBA

[CodeUser1549]

Hello Everyone,

I have a fairly standard macro that uses autofilters using different variables to filter out the data, but then after that it needs to manipulate the rows that still visible, I have found this basic code from other posts that moves down the data untill it reaches a visible row:

Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop

This code works fine but the problem is that its a very large data set (up to 26,000 rows) so even using a macro takes a fairly long amount of time to find the visible rows using this method (ex. when row 25000 250001 and 250002 are the only ones that meet the criteria it has to go from 1 to 25000 row by row). Is there any code that is possibly more efficent then that above.

Thanks

 

[JackDanIce]

Hi and welcome to the board.

Not sure if this will work when rows are hidden, but try:

Range("A" & Rows.Count).End(xlUp).Select

Assuming column A has data in it

 

[CodeUser1549]

Jack,

Unfortunately using that piece does not quite complete my code. It will bring me to the last filtered row of data, but I need it to bring me to the first row of data in the listing and they are not always necessarily next to each other or the same number of rows.

Thanks

 

[fudged]

First Row of Filtered Rows

Sub FirstFilteredRow()
Dim Str1 As String
Dim Rng1, Rng2, LastCell, FirstCell As Range
Dim clmns As Integer

clmns = ActiveSheet.AutoFilter.Range.Columns.Count
Str1 = ActiveSheet.AutoFilter.Range.Offset(0).SpecialCells(xlCellTypeVisible).Address
Areas = Split(Str1, ",", -1)
Set Rng1 = ActiveSheet.Range(Areas(0))
Set FirstCell = Rng1(clmns + 1)
If FirstCell.EntireRow.Hidden Then
Set Rng1 = ActiveSheet.Range(Areas(1))
Set FirstCell = Rng1(1)
End If

MsgBox ("First filtered row = " & FirstCell.Row)
End Sub