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

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

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.

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.

Determine Cells(5,4) IN SPITE of hidden rows/columns
 

bump

Determine Cells(5,4) IN SPITE of hidden rows/columns
 

On Feb 22, 2:50 pm, "Finny" wrote:
bump

no thoughts?

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?

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!