VBA for visible range

fi3ry_ph03n1x

New Member
Mr. Urtis,

I have to say first that your coding capabilities are quite impressive. I've used this code successfully, and it is exactly what I need. (I'm writing byval worksheet_change code to move macro-linked button images down the right margin in order to stay visible as users progress through a log).

I'm writing, though, because I have a specific problem. I'm not building my own worksheet and am constrained to a specific layout that is mandated by my employer. But I can make some adjustments (in VBA particularly), so long as the sheet *looks* the same. The sheet I'm working with is using a split screen to maintain a heading bar above the data, but it is a thin row and must remain the same...so I can't put my buttons IN the header row as they won't fit.

I've discovered that your code here only works without split screen turned on. Is there any way to modify this code to apply to the pane (windows.panes(3), specifically) so that it functions with the split screen enabled? If not, is there some other solution to capture the range address of the top-left cell that is currently on screen (skipping the "heading" Windows.Panes(1)....Row(53))? If it's important, I have a horizontal pane split, and nothing vertical.

Thanks much for lending your expertise. My coworkers think I'm a genius in Excel, but the truth is that I know just enough to impress them and see real quality coding when I see it. :) Your work is much appreciated.
 

Tom Urtis

MrExcel MVP
Thank you for the kind words! Gee, I'd forgotten about this thread after its 12-year dormancy.

I just wanted to get back to you with this reply to say that I would have to give this some thought for reliable code when dealing with split screens. It seems there would be 3 possibilities: (1) No split screen, (2) Two split screens if only a vertical or horizontal split is enacted, or (3) Four split screens if both the vertical and horizontal splits are enacted. I'll try to give it some thought over the weekend, but if anyone reading this wants to to jump in, please feel free to do so.
 

fi3ry_ph03n1x

New Member
Thank you for the kind words! Gee, I'd forgotten about this thread after its 12-year dormancy.

I just wanted to get back to you with this reply to say that I would have to give this some thought for reliable code when dealing with split screens. It seems there would be 3 possibilities: (1) No split screen, (2) Two split screens if only a vertical or horizontal split is enacted, or (3) Four split screens if both the vertical and horizontal splits are enacted. I'll try to give it some thought over the weekend, but if anyone reading this wants to to jump in, please feel free to do so.
Thank you. I know it is an ancient thread so far as tech stuff goes. But, surprisingly, I haven't found anywhere where this question has been answered satisfactorily. I couldn't in good conscience look for more than about 5-6 hours before putting it on hold (and didn't have a great deal of hope of hearing back on this thread given its age).

Consider this a hobbyist challenge. I no longer have to use this solution, so only give it some thought if you feel like challenging yourself and would enjoy the experience. I appreciate the reply.
 

Rick Rothstein

MrExcel MVP
This code produces a slightly different result than Tom's code (either originally or if expanded for split screens). Tom's code only counted a row or column if it was fully visible within its pane choosing to ignore partially exposed rows or columns even if they were almost fully exposed show all of their data as readable. My code, on the other hand, counts a row or column within its pane even if only the tiniest sliver of the row or column is visible even if no data is readable. Each method has its advantages and disadvantages. The reason I chose the method I did is because that is how VBA counts things via its ActiveWindow object. With that said, here is my code which will work for 1, 2 or 4 visible panes...
Code:
Sub MyWindowArea2()
  Dim VisibleRange As String, NumberOfRows As Long, NumberOfCols As Long, P As Long
  For P = 1 To ActiveWindow.Panes.Count
    With ActiveWindow.Panes(P).VisibleRange
      VisibleRange = VisibleRange & ", " & .Address(0, 0)
      NumberOfRows = NumberOfRows + Intersect(.Columns(1), .SpecialCells(xlVisible).EntireRow).Count
      NumberOfCols = NumberOfCols + Intersect(.Rows(1), .SpecialCells(xlVisible).EntireColumn).Count
    End With
  Next
  VisibleRange = Mid(VisibleRange, 3)
  Select Case ActiveWindow.Panes.Count
    Case 2
      If ActiveWindow.Panes(1).VisibleRange.Columns.Count = ActiveWindow.Panes(2).VisibleRange.Columns.Count Then
        NumberOfCols = NumberOfCols / 2
      Else
        NumberOfRows = NumberOfRows / 2
      End If
    Case 4
      NumberOfRows = NumberOfRows / 2
      NumberOfCols = NumberOfCols / 2
  End Select
  MsgBox "Visible Range: " & VisibleRange & vbLf & _
         "Count of Visible Rows: " & NumberOfRows & vbLf & _
         "count of Visible Columns: " & NumberOfCols
End Sub
 
Last edited:

fi3ry_ph03n1x

New Member
That's fantastic, Mr. Rothstein. It isn't at all important to me whether we are capturing partial columns/rows, or fully displayed one, so I can definitely utilize this code. I do have one problem though.

I'm inexperienced enough with VBA that I am unable to capture the variable I need, even though I can clearly see it displayed in your code results. Though it embarrasses me to ask, I could really use some help in understanding how I can extract the specific information I need for use in other subs. I screen capped the results for simplicity and uploaded the photo here:

https://photos.app.goo.gl/3nQHPspVreB79cMf9

You'll see that Row(53) is my header where the screen splits. It's the row immediately below it (A60 in this case) that I need to reference. I see the Pane(3) range shown in your msgbox, but I don't know how to access that cell/range to use elsewhere. Could you help me with a primer on accessing this data in other subs?

Thanks so much,


Mark Burns
 

Rick Rothstein

MrExcel MVP
You'll see that Row(53) is my header where the screen splits. It's the row immediately below it (A60 in this case) that I need to reference. I see the Pane(3) range shown in your msgbox, but I don't know how to access that cell/range to use elsewhere. Could you help me with a primer on accessing this data in other subs?
You don't need all that code I posted for what you are asking above. This single line of code with return the address for the top left cell in Pane 3...
Code:
TopLeftCellInPane3 = ActiveWindow.Panes(3).VisibleRange(1).Offset(1).Address
 

fi3ry_ph03n1x

New Member
You don't need all that code I posted for what you are asking above. This single line of code with return the address for the top left cell in Pane 3...
Code:
TopLeftCellInPane3 = ActiveWindow.Panes(3).VisibleRange(1).Offset(1).Address
Maybe I'm doing something wrong, but this singular line returns the error: "Method 'VisibleRnage' of object 'Pane' failed. (Run-time error '-2147417848 (80010108)).
 

Rick Rothstein

MrExcel MVP
Maybe I'm doing something wrong, but this singular line returns the error: "Method 'VisibleRnage' of object 'Pane' failed. (Run-time error '-2147417848 (80010108)).
I didn't look at your file... do you have the screen split into 4 panes? If you only have it split into two panes, then change the 3's to 2's...
Code:
TopLeftCellInPane[B][COLOR="#FF0000"]2[/COLOR][/B] = ActiveWindow.Panes([B][COLOR="#FF0000"]2[/COLOR][/B]).VisibleRange(1).Offset(1).Address
Just so you know, the only possible number of panes is 1, 2 or 4... you cannot have 3 panes.
 

fi3ry_ph03n1x

New Member
I didn't look at your file... do you have the screen split into 4 panes? If you only have it split into two panes, then change the 3's to 2's...
Code:
TopLeftCellInPane[B][COLOR=#FF0000]2[/COLOR][/B] = ActiveWindow.Panes([B][COLOR=#FF0000]2[/COLOR][/B]).VisibleRange(1).Offset(1).Address
Just so you know, the only possible number of panes is 1, 2 or 4... you cannot have 3 panes.
Totally makes sense that there can only be the three options for panes, but for some reason, when I recorded a macro to capture the code for activating panes (in the "horizontal split only" setup), it recorded the bottom one as a Pane(3) activation.

In any case, I've been able to use your code in my worksheet successfully, and I want to thank you very sincerely for sharing your time and experience to help others benefit from what you've learned. :)
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top