Select visible cells using vba

[Guest]

Hi there,

Using vba code, I want to select a cell that has been filtered?

ie.
Range("A1").select
Activecell.offset(2,0).select - this selects Range("A3") but is not
visible.

I want it to consider visible cells only.

Any ideas?

 

[Tom Ogilvy]

Range("A1").Select
do
ActiveCell.offset(1,0).Select
Loop While ActiveCell.EntireRow.Hidden = True

Did you want to select the second visible cell below the current selection
rather than the next visible cell?

 

[Guest]

Thanks Tom, this is great.
To select a cell further down the filtered range (ie the 2nd visible cell),
should I just repeat the do loop function, or is there a better way?

In addition to selecting the 2nd visible cell, is there an easy way to copy
just this entire row?

Thanks again, this is a big help!
Cheers,
Tony

 

[Tom Ogilvy]

1) To continue down the filtered range, use the same method.

If you want to loop throught the visible range and are using an autofilter
in this example, copy filtered rows beginning with "ABC" to another sheet

Dim cell as Range, rng as Range
Dim rng1 as Ragne
set rng = Activesheet.Autofilter.Range.Columns(1)
set rng = rng.Offset(1,0).Resize(rng.rows.count -1,1)
On Error Resume Next
set rng1 = rng.specialCells(xlvisible)
On Error goto 0
if rng1 is nothing then
msgbox "No visible rows"
else
for each cell in rng1
if left(cell.Value,3) = "ABC" then
cell.Entirerow.Copy Destination:= _
Worksheets("Othersheet").Cells(rows.count,1).End(xlup)(2)
end if
Next
End if

--------------------
2) To copy:

ActiveCell.Entirerow.copy Destination:= _
Worksheets("Othersheet").Cells(rows.count,1).End(xlup)(2)

All code is untested and may contain typos.