Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Offset to the next visible cell

Status
Not open for further replies.

ChrizDK

IS-IT--Management
Dec 11, 2002
7
DK
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
 
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

Code:
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

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

Code:
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? [ponder]
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244
 
I like your solution Loomah.

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

Best regards,

Christian
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top