Select range of visible cells

[flowtime]

Hello all,

I need a little help.

I'm trying to copy the top 5 lines of a filtered table.

Range("A3").Select

'Next visible cell start
ActiveCell.Offset(1, 0).Activate
Do While ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Activate
Loop
'Next visible cell end

ActiveCell.Offset(1, 0).Resize(5, 1).Copy

A3 is the top of the table. The first loop takes it down to the first visible cell.

but I can't figure out how to get the offset command to only include visible fields. In this case I have something in A19 and then nothing else until A35 so this command only copies 1 line instead of 5.

[mancubus]

welcome to the forum...

so the headers are in row 3?

run below code and open Immediate Window in VBE to see why Resize, etc fail...

Sub FilteredRowNumbers()
    With Worksheets("MySheet") 'change MySheet to suit
        .AutoFilterMode = False
        .Range("A3").AutoFilter Field:=2, Criteria1:="=MyCrit"
        For Each cll In .UsedRange.Columns(1).SpecialCells(xlCellTypeVisible)
            Debug.Print cll.Row
        Next
        .AutoFilterMode = False
    End With
End Sub

[mancubus]

below code copies all the auto filter range and the clears the cells below top Nth row.

Sub vbax_53926_Copy_Top_N_Rows_AutoFilterRange()
    Dim TopN As Long
    
    TopN = 5
    
    With Worksheets("MySheet") 'change MySheet to suit
        .AutoFilterMode = False
        .Range("A3").AutoFilter Field:=2, Criteria1:="=MyCrit"
        .AutoFilter.Range.Copy Destination:=Worksheets("MySheet2").Range("A1")
        .AutoFilterMode = False
    End With
    
    Worksheets("MySheet2").UsedRange.Offset(TopN).Clear
End Sub

[flowtime]

below code copies all the auto filter range and the clears the cells below top Nth row.

Sub vbax_53926_Copy_Top_N_Rows_AutoFilterRange()
    Dim TopN As Long
    
    TopN = 5
    
    With Worksheets("MySheet") 'change MySheet to suit
        .AutoFilterMode = False
        .Range("A3").AutoFilter Field:=2, Criteria1:="=MyCrit"
        .AutoFilter.Range.Copy Destination:=Worksheets("MySheet2").Range("A1")
        .AutoFilterMode = False
    End With
    
    Worksheets("MySheet2").UsedRange.Offset(TopN).Clear
End Sub

Hi Thanks for the response

In response to your first post. None of the original script is throwing an actual error. It is just copying and pasting the wrong thing. This portion

ActiveCell.Offset(1, 0).Resize(5, 1).Copy

is going down copying the next 5 cells down, but if some of those cells are hidden it copies those and not just the visible cells in the filtered table.



The second code you sent with the TopN = 5 statement gets me closer to what I want, but I only need the one column out of the table and not the whole table.

[mancubus]

welcome.
pls dont quote the whole message or code.

yes, it does not throw an error; it just fails to do what you are after.

change
.AutoFilter.Range.Copy

to
.AutoFilter.Range.Columns(1).Copy

to copy Column A of filtered table.

as you may know, Columns(1) is Column A, Columns(2) is Column B, Columns(5) is Column E, etc.

[flowtime]

Perfect! That got me where I needed to be. I had the longest workaround. You just cut processing time of the macro down by like 45 seconds. Thank you!

[mancubus]

you are welcome.
please mark the thread as solved from thread tools for future references...