I spent hours trying to figure out and gave up and came here for some help.
This is what I am trying to do.
I use the following code to find out cell address of freeze pane:
Let say Freezepane is set at cell D4
Function Get_Curr_FreezePaneAdd_Of_ActiveSheet () as string
Get_Curr_FreezePaneAdd_Of_ActiveSheet = Cells(ActiveWindow.SplitRow + 1, ActiveWindow.SplitColumn + 1).Address
Get_Curr_FreezePaneAdd_Of_ActiveSheet = Replace(Get_Curr_FreezePaneAdd_Of_ActiveSheet, "$", "")
end Function
It perfectly gives me right cell address D4
But let say column A is scrolled off the screen before the freeze pane was set, it gives me wrong column number. It would give me C4.
Now to compensate for this, I figured if I can check for scrolled of column by using intersect with visible range like this:
ColmIsVisible = Not Intersect(ActiveWindow.VisibleRange, ActiveSheet.Cells(1, 1)) Is Nothing
OR
ColmIsVisible = Not Intersect(ActiveWindow.VisibleRange, ActiveSheet.range("A1")) Is Nothing
I use loop to check for first 3 (C4) columns if they are visible.
I found out that intersect with visible range doesn't work on columns that a frozen. It considers them like they are scrolled off screen.
It gives me false for all columns. Column A which is off screen and also false for column B and C.
How do I find out scrolled off columns left of freeze pane cell?
I even tried to check for column width. If they are 0 if off screen but that's only if the column is hidden.
Who can help me?
Bookmarks