Select visible cells using vba

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?

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?

--
Regards,
Tom Ogilvy


"Tony" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

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" wrote:

>
> 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?
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Tony" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
>
>
>

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.

--
Regards,
Tom Ogilvy

"Tony" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> >
> > 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?
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Tony" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> > >
> >
> >
> >