How to test for a Row being Visible on a Spreadsheet
 

Hi,

I am a newbie to this group but I have been unable to find a
solution to my problem, so I decided to post it here in the hope that
someone might be able to help.


Issue :

I have a spreadsheet which contains data in rows 6 through 22.
However, some rows have been 'frozen' (i.e. by taking Windows /
Unfreeze they re-appear).
If I look at the sheet, the rows are numbered 5 and then 15. Rows 6
to 14 inclusive are not visible

I am trying to programatically exclude any rows that are not
visible.

I have tried the following lines of code to no avail :

"If Worksheets(strsheetname).Rows(inti).Visible = True Then x = 1 Else
x = 2"

"If Worksheets(strsheetname).Rows(inti).Hidden = True Then x = 1 Else
x = 2"

I also did some trials using the property Visiblerange but once
again, it wasn't coming back as false which would allow me to set the
flag I require.


Thanks in advance for any suggestions,


Niall Heelan

How to test for a Row being Visible on a Spreadsheet
 

..SpecialCells(xlCellTypeVisible)

--
Dan Oakes

How to test for a Row being Visible on a Spreadsheet
 

On Oct 16, 2:27 pm, "Dan R." wrote:
.SpecialCells(xlCellTypeVisible)

--
Dan Oakes


Hi Dan, thanks for the quick response.

The following is a snippet of my effort to use this property but
unfortunately the test for row 15 is coming back as nothing (as is the
test for row 6 - which is what I want) :

Dim ran1 As Range
Dim ran2 As Range

Set ran1 = Nothing
Set ran2 = Nothing

On Error Resume Next
ran1 = Rows("6").SpecialCells(xlCellTypeVisible)

If ran1 Is Nothing Then
MsgBox "This is a good thing"
Else
MsgBox "This is a bad thing"
End If

ran2 = Rows("15").SpecialCells(xlCellTypeVisible)

If ran2 Is Nothing Then
MsgBox "This is a bad thing"
Else
MsgBox "This is a good thing"
End If

Thanks,

Niall

How to test for a Row being Visible on a Spreadsheet
 

Try your code this way...

Dim ran1 As Range
Dim ran2 As Range

Set ran1 = Nothing
Set ran2 = Nothing

Set ran1 = Intersect(Rows(6), Cells.SpecialCells(xlCellTypeVisible))

If ran1 Is Nothing Then
MsgBox "6 is hidden"
Else
MsgBox "6 is not hidden"
End If

Set ran2 = Intersect(Rows(15), Cells.SpecialCells(xlCellTypeVisible))

If ran2 Is Nothing Then
MsgBox "15 is hidden"
Else
MsgBox "15 is not hidden"
End If


Rick


" wrote in message
oups.com...
On Oct 16, 2:27 pm, "Dan R." wrote:
.SpecialCells(xlCellTypeVisible)

--
Dan Oakes


Hi Dan, thanks for the quick response.

The following is a snippet of my effort to use this property but
unfortunately the test for row 15 is coming back as nothing (as is the
test for row 6 - which is what I want) :

Dim ran1 As Range
Dim ran2 As Range

Set ran1 = Nothing
Set ran2 = Nothing

On Error Resume Next
ran1 = Rows("6").SpecialCells(xlCellTypeVisible)

If ran1 Is Nothing Then
MsgBox "This is a good thing"
Else
MsgBox "This is a bad thing"
End If

ran2 = Rows("15").SpecialCells(xlCellTypeVisible)

If ran2 Is Nothing Then
MsgBox "This is a bad thing"
Else
MsgBox "This is a good thing"
End If

Thanks,

Niall

How to test for a Row being Visible on a Spreadsheet
 

Are rows 6 through 14 hidden, or has the row heigth been set to zero?
Check the row heigth for row 5 and then change rows 5 through 15 to
the same value. Don't know if that will hep, but it will eliminate on
possibility. -pb

How to test for a Row being Visible on a Spreadsheet
 

The code I posted appears to work if either the row is hidden or if its
height has been set to 0.

Rick


"cubbybear3" wrote in message
ups.com...
Are rows 6 through 14 hidden, or has the row heigth been set to zero?
Check the row heigth for row 5 and then change rows 5 through 15 to
the same value. Don't know if that will hep, but it will eliminate on
possibility. -pb

How to test for a Row being Visible on a Spreadsheet
 

Hi Niall,

You could also try -
rw = 8
bVis = Not ActiveSheet.Rows(rw).EntireRow.Hidden
MsgBox "Row " & rw & " is Visible =" & bVis

However, some rows have been 'frozen' (i.e. by taking Windows /
Unfreeze they re-appear).

So are you really looking for hidden/visible rows, or perhaps instead you
mean rows below the top pane but above the top cell in the bottom pane,
giving the impression of being hidden depending on the vertical scroll in
the bottom pane. If so, start with something like the following -

Dim wn as Window
Set wn = Activewindow
For i = 1 To wn.Panes.Count
With wn.Panes(i).VisibleRange
Debug.Print .Rows(1).Row, .Rows.Count + .Rows(1).Row - 1
End With
Next

Above debugs the top & bottom rows in each pane. Assuming FreezePanese is a
horizontal split only, as confirmed by wn.FreezxePanes = True &
wn.SplitHorizontal = 0, there will be two panes. Any apparently non visible
rows will be those between the bottom row of the top pane and the top row of
the bottom pane.

Of course there may also be 'Hidden' rows in either pane, ie those with a
row height of 0.

Regards,
Peter T







" wrote in message
oups.com...

Hi,

I am a newbie to this group but I have been unable to find a
solution to my problem, so I decided to post it here in the hope that
someone might be able to help.


Issue :

I have a spreadsheet which contains data in rows 6 through 22.
However, some rows have been 'frozen' (i.e. by taking Windows /
Unfreeze they re-appear).
If I look at the sheet, the rows are numbered 5 and then 15. Rows 6
to 14 inclusive are not visible

I am trying to programatically exclude any rows that are not
visible.

I have tried the following lines of code to no avail :

"If Worksheets(strsheetname).Rows(inti).Visible = True Then x = 1 Else
x = 2"

"If Worksheets(strsheetname).Rows(inti).Hidden = True Then x = 1 Else
x = 2"

I also did some trials using the property Visiblerange but once
again, it wasn't coming back as false which would allow me to set the
flag I require.


Thanks in advance for any suggestions,


Niall Heelan

How to test for a Row being Visible on a Spreadsheet
 

Set ran1 = Intersect(Rows(6), Cells.SpecialCells(xlCellTypeVisible))


Thanks to all for your replies.

Rick's intersect statement above works fine.

Keep up the good work.


Niall H