PDA

View Full Version : [SOLVED] Select range of visible cells



flowtime
10-06-2015, 08:50 AM
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
10-06-2015, 11:44 PM
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
10-06-2015, 11:48 PM
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
10-07-2015, 06:17 AM
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
10-07-2015, 06:39 AM
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
10-07-2015, 06:59 AM
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
10-07-2015, 07:30 AM
you are welcome.
please mark the thread as solved from thread tools for future references...