# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  > [SOLVED] Select visible cells using vba

## Tony

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?

--
Regards,
Tom Ogilvy


"Tony" <Tony@discussions.microsoft.com> wrote in message
news:ECE5AFE0-E477-42E7-A8DB-D8794D4DE0B3@microsoft.com...
> 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?
>

----------


## Tony

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" <Tony@discussions.microsoft.com> wrote in message
> news:ECE5AFE0-E477-42E7-A8DB-D8794D4DE0B3@microsoft.com...
> > 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

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" <Tony@discussions.microsoft.com> wrote in message
news:D220BA42-D047-47D1-93F7-E6D30C8993A2@microsoft.com...
> 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" <Tony@discussions.microsoft.com> wrote in message
> > news:ECE5AFE0-E477-42E7-A8DB-D8794D4DE0B3@microsoft.com...
> > > 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?
> > >
> >
> >
> >

----------

