Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to test for a Row being Visible on a Spreadsheet
..SpecialCells(xlCellTypeVisible) -- Dan Oakes |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i make more columns visible in a spreadsheet? | Excel Discussion (Misc queries) | |||
spreadsheet not visible | Excel Discussion (Misc queries) | |||
excel spreadsheet not visible | Excel Discussion (Misc queries) | |||
Word art and drawings not visible in spreadsheet | Excel Discussion (Misc queries) | |||
Autoshapes not visible on spreadsheet but visible in print preview | Excel Discussion (Misc queries) |