Offset to the next visible cell

[ChrizDK]

Hello,

i tried searching for an answer to this but couldn't find one. If it's already covered i appologize for my ignorance.

I'm using the Offset function to move through my cells:

EXAMPLE: ActiveCell.Offset([-1], [0]).Select

This will move down to the next cell in my spreadsheet.

If i use a filter, would it be possible to only offset to the next VISIBLE cell? - thereby saving a considerable amount of time...

Best wishes

Christian

 

[Chance1234]

you could check to see if the row was hidden

if the row was hidden move down to the next row and repeat the check, im writing this blind so may need tweaking

Dim bln_ishidden
Dim int_row as integer
int_row = -1 
do until bln_ishidden = true 

If ActiveCell.Offset(int_row, [0]).Range.Hidden = True then 
      int_row = int_row - 1 
else 
    bln_ishidden = true 
    ActiveCell.Offset(int_row, [0]).select 
end if

Filmmaker, gentleman and i have a new site 3/4 working

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[Loomah]

Here's another slightly different way to skin this particular cat

Dim r, c 'properly
r = activecell.row
c = activecell.Column
Do
r = cells(r, c).Offset(1, 0).row
Loop until rows(r).hidden = false
Cells(r, c).select

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Chance1234]

thats a nice tidy solution from loomah there chriz,

Filmmaker, gentleman and i have a new site 3/4 working

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[ChrizDK]

I like your solution Loomah.

Will try to implement it and see how it works out.

Best regards,

Christian

 

[PHV]

You may also consider the SpecialCells(xlCellTypeVisible) method of the Range object.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884