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.

 

[Norie]

How do they don't quite get it done?

Have you tried Columns.Count and Rows.Count?

Or even Address?

 

[Gates Is Antichrist]

Very funny "Don't quite get it done" means doesn't quite give me what I want. What I want is what I asked for. What I ask for is subservient to what I really want. (And oh yeah, never forget, do what I think, not what I say )

Thanks for the tips. Yes I tried those. Did you?

Let me try asking another way: I'm looking for the row and column number, or number of rows and columns, that are visible in activewindow.

 

[Norie]

No of course I didn't try them, that would've been a bit amatuerish don't you think. :wink:

PS You do realise that there might be quite a few cells visible in the active window.

 

[Tom Urtis]

I'm looking for the row and column number, or number of rows and columns, that are visible in activewindow.

This would do that, and for the anti-Activate mavens, I purposely used the Activate method here.

Sub MyWindowArea()
With ActiveWindow

Dim rng As Range, xRow&, iCol%, xRowStart&, iColStart%, vizRows&, vizCols&, x&
vizRows = 0: vizCols = 0

Set rng = ActiveCell
iColStart = .ScrollColumn
iCol = iColStart
   
Do Until .ScrollColumn <> iColStart
ActiveCell.Offset(0, 1).Activate
Loop
   
iColStart = ActiveCell.Column - 1
xRowStart = .ScrollRow
xRow = xRowStart
   
Do Until .ScrollRow <> xRowStart
ActiveCell.Offset(1, 0).Select
Loop
xRowStart = ActiveCell.Row - 1
rng.Select

For x = xRow To xRowStart
If Rows(x).Hidden = False Then vizRows = vizRows + 1
Next x

For x = iCol To iColStart
If Columns(x).Hidden = False Then vizCols = vizCols + 1
Next x

End With

MsgBox "Visible range: " & Range(Cells(xRow, iCol), Cells(xRowStart, iColStart)).Address(0, 0) & vbCrLf & _
"Count of visible rows: " & vizRows & vbCrLf & _
"Count of visible columns: " & vizCols, , "Active window info"

End Sub

 

[Gates Is Antichrist]

Holy crap, do you always get it right on the first try, Tom? I like to make at least a couple of mistakes on the first whack! Really, that's a lot of work. I didn't know so much would be set off; I thought more scroll area type properties would be available. Thank you for your generous time and effort, and again, taking the time to get it right.

I will offer one tweak. Right before rng.select add
Application.Goto Reference:=Range("A1").Offset(xRow - 1, iCol - 1)
so that the window reorients. Excel tries to "centralize" cell navigation and in testing, rng.select didn't restore the upper left corner identically. Note that
Range("A1").Offset(xRow - 1, iCol - 1).select
or
Range("A1").Offset(xRow - 1, iCol - 1).Activate
doesn't get it done, because neither of those cause the reorientation. It's as though those two simply don't execute, as though they get cached and discarded by the ensuing rng.select. If so, that's a sweet optimization they have there, but it can be defeated with the GoTo.

Jeez, thanks again Tom.

You know, I've tested this with window-freeze (I haven't tried splits) and it's not intimidated! Way to go!

 

[Tom Urtis]

Thanks for the enhancement suggestion, which actually I had noticed a need for in some cases as you found out, but did not write, so you helped me out too. Looks like a win-win, thanks again.

 

[Gates Is Antichrist]

Well I got lucky in noticing that you went down and right, so I aimed for the upper left corner. Now that I think about it, though, in a general case of reorientation, I could aim at both the lower right and upper left(successively), and that'll square things off regardless

 

[Tom Urtis]

Just thinking more about this, that unwanted movement can be dealt with per the below simple modifications. Please let me know if you discover any scenarios where this does not work, so far it works OK for me.

Sub MyWindowArea2()
With ActiveWindow

Dim rng As Range, xRow&, iCol%, xRowStart&, iColStart%, vizRows&, vizCols&, x&
Dim StartAddress$
vizRows = 0: vizCols = 0
StartAddress = ActiveCell.Address

Set rng = ActiveCell
iColStart = .ScrollColumn
iCol = iColStart
   
Do Until .ScrollColumn <> iColStart
ActiveCell.Offset(0, 1).Activate
Loop
   
iColStart = ActiveCell.Column - 1
xRowStart = .ScrollRow
xRow = xRowStart
   
Do Until .ScrollRow <> xRowStart
ActiveCell.Offset(1, 0).Select
Loop
xRowStart = ActiveCell.Row - 1
rng.Select

For x = xRow To xRowStart
If Rows(x).Hidden = False Then vizRows = vizRows + 1
Next x

For x = iCol To iColStart
If Columns(x).Hidden = False Then vizCols = vizCols + 1
Next x

End With

Application.Goto Cells(xRow, iCol), 1
Range(StartAddress).Select

MsgBox "Visible range: " & Range(Cells(xRow, iCol), Cells(xRowStart, iColStart)).Address(0, 0) & vbCrLf & _
"Count of visible rows: " & vizRows & vbCrLf & _
"Count of visible columns: " & vizCols, , "Active window info"

End Sub

 

[Gates Is Antichrist]

The goto is great, and obviously classier than mine.

As things stand the code doesn't play well with split windows (e.g. the lower right one will fail reliably, whatever that means ) . I don't know what would resolve that yet, but .SplitColumn, .Splitrow and .split are available so I think it'll fall into place.

I'll play with it when I get time if it's still unsolved later.