VBA to find first visible cell

[seguin85]

Hi All,

I've been looking for a while through numerous threads to find a solution to my problem. Anyways I am grabbing information from another workbook from another department and the cells where I need the information are always changing. I need to be able to select the first visible cell and set my workbook to this cell (it is always a date). This date changes depending on when the information has been updated. The old dates are hidden. So I want a line of code that can help me find this.

This is one of the iterations that I've tried so far...

Range("P4").Select
Do Until UsedRange.SpecialCells(xlCellTypeVisible)
ActiveCell.Offset(0,1).Select
Loop

Any help would be appreciated.

 

[Lee.]

First visible as in actually showing, or the first cell to contain data?

 

[seguin85]

First cell to be showing. They manually hide all the columns of previously passed dates

 

[Rick Rothstein]

I need to be able to select the first visible cell and set my workbook to this cell (it is always a date).

Assuming your dates are in Column A...

Range("A2:A" & Rows.Count).SpecialCells(xlVisible)(1).Select

Change the red 2 to the row number where the first hidden row could possibly be located at. I used 2 because I figured Row 1 would be a header row and header rows are always visible, you it needs to be skipped over. If you have three header rows, then you would change the red 2 to 4 (the first row that could possibly be hidden). I would note, though, that it is rarely necessary to select cells in order to do things with them.

 

[seguin85]

Thanks Rick!!

With a little tweaking I made it work... The columns were hidden not the rows. So I used

Range("P4:ZZ4" & Columns.Count).SpecialCells(xlVisible)(1).Select