VBA for visible range

[Gates Is Antichrist]

I'm looking for the row and column number, or number of rows and columns, that are visible in activewindow. Activewindow.visiblerange.row, .column (and .Scrollrow, .Scrollcolumn) are nice but don't quite get it done.

I prefer not to perform division on pixel height; I'm looking for some count or rowcount but I haven't been able to locate the object.

 

[fi3ry_ph03n1x]

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]

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]

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]

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...

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

 

[fi3ry_ph03n1x]

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]

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...

TopLeftCellInPane3 = ActiveWindow.Panes(3).VisibleRange(1).Offset(1).Address

 

[fi3ry_ph03n1x]

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...

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]

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...

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]

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...

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.