Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine Cells(5,4) IN SPITE of hidden rows/columns
I have a sheet that toggles hidden values of certain rows and columns
to accommodate the views of data for different users. Trouble is, in addition to this, some rows are permanently hidden as once they are too old - but not deleted to keep as reference. In each of the views, position of the freeze panes is different. Always in the same position in the eyes of the user, but a different cell every time an old row becomes hidden. In a nutshell, with a sheet with unknown hidden rows/columns, how could you pinpoint position (5,4) according to what you see, not what is both shown and hidden? thanks in advance and I hope that is clear enough |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine Cells(5,4) IN SPITE of hidden rows/columns
ActiveWindows.visibleRange(5,4)
assume that rows 1 - 7 are scrolled out of site and row 8 is frozen further, columns A-C are scrolled out of site and column D is frozen this puts E9 as the upper left corner of the visible range. then ? activeWindow.visiblerange.address $E$9:$Q$46 ? activeWindow.VisibleRange(5,4).Address $H$13 Just note that the (5,4) will be relative to the upper left corner of the visible range and would be different if user scrolls. -- Regards, Tom Ogilvy "Finny" wrote: I have a sheet that toggles hidden values of certain rows and columns to accommodate the views of data for different users. Trouble is, in addition to this, some rows are permanently hidden as once they are too old - but not deleted to keep as reference. In each of the views, position of the freeze panes is different. Always in the same position in the eyes of the user, but a different cell every time an old row becomes hidden. In a nutshell, with a sheet with unknown hidden rows/columns, how could you pinpoint position (5,4) according to what you see, not what is both shown and hidden? thanks in advance and I hope that is clear enough |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine Cells(5,4) IN SPITE of hidden rows/columns
What I'm trying to do in whole is hide certain rows/cols and reset
freeze pane position in two setups and toggle between the two. I'm getting a 424 on the 3rd line where I set turn off FreezePanes. I don't understand, I thought with the activate, the object is qualified. Also, I'm not sure if the way I'm selecting will freeze panes properly. Private Sub Order_Click() Application.ScreenUpdating = False Workbooks("Purchase Orders Calendar.xls").Worksheets("Sheet1").Activate ActiveWindows.FreezePanes = False With Workbooks("Purchase Orders Calendar.xls").Worksheets("Sheet1") If .Columns("AD:AF").Hidden Then .Columns("AD:AF").Hidden = False .Rows("1:23").Hidden = False ''''Make selection (ROW) to freeze panes .VisibleRange(2, 2).Select .Selection.Row.Select Else .Columns("AD:AF").Hidden = True .Rows("1:23").Hidden = True ''''Make selection (CELL) to freeze panes .VisibleRange(2, 25).Select End If End With ActiveWindows.FreezePanes = True Application.ScreenUpdating = True End Sub I thought this would be relatively simple, I guess it depends on experience. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine Cells(5,4) IN SPITE of hidden rows/columns
On Feb 21, 9:34 am, "Finny" wrote:
What I'm trying to do in whole is hide certain rows/cols and reset freeze pane position in two setups and toggle between the two. I'm getting a 424 on the 3rd line where I set turn off FreezePanes. I don't understand, I thought with the activate, the object is qualified. Also, I'm not sure if the way I'm selecting will freeze panes properly. Private Sub Order_Click() Application.ScreenUpdating = False Workbooks("Purchase Orders Calendar.xls").Worksheets("Sheet1").Activate ActiveWindows.FreezePanes = False With Workbooks("Purchase Orders Calendar.xls").Worksheets("Sheet1") If .Columns("AD:AF").Hidden Then .Columns("AD:AF").Hidden = False .Rows("1:23").Hidden = False ''''Make selection (ROW) to freeze panes .VisibleRange(2, 2).Select .Selection.Row.Select Else .Columns("AD:AF").Hidden = True .Rows("1:23").Hidden = True ''''Make selection (CELL) to freeze panes .VisibleRange(2, 25).Select End If End With ActiveWindows.FreezePanes = True Application.ScreenUpdating = True End Sub I thought this would be relatively simple, I guess it depends on experience. btw, thanks for the reply Tom. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine Cells(5,4) IN SPITE of hidden rows/columns
bump
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine Cells(5,4) IN SPITE of hidden rows/columns
On Feb 22, 2:50 pm, "Finny" wrote:
bump no thoughts? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine Cells(5,4) IN SPITE of hidden rows/columns
where you have
ActiveWindows.FreezePanes = False Use ActiveWindow without the "s" same with ActiveWindows.FreezePanes = True change to: ActiveWindow.FreezePanes = True -- Regards, Tom Ogilvy "Finny" wrote in message oups.com... On Feb 22, 2:50 pm, "Finny" wrote: bump no thoughts? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine Cells(5,4) IN SPITE of hidden rows/columns
On Feb 25, 2:47 pm, "Tom Ogilvy" wrote:
where you have ActiveWindows.FreezePanes = False Use ActiveWindow without the "s" same with ActiveWindows.FreezePanes = True change to: ActiveWindow.FreezePanes = True -- Regards, Tom Ogilvy "Finny" wrote in message oups.com... On Feb 22, 2:50 pm, "Finny" wrote: bump no thoughts? That did the trick Tom, thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i paste rows/columns avoiding hidden rows/columns | Excel Discussion (Misc queries) | |||
Hidden rows/columns expand when referencing cells? | Excel Worksheet Functions | |||
Hidden rows columns won't stay hidden | Excel Worksheet Functions | |||
determine if there are hidden rows in a column | Excel Programming | |||
hidden rows/columns | Excel Discussion (Misc queries) |