VBA for visible range

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
How do they don't quite get it done?

Have you tried Columns.Count and Rows.Count?

Or even Address?
 
Upvote 0
Very funny o_O "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. :unsure: (And oh yeah, never forget, do what I think, not what I say :devilish: )

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

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.

:oops:
:oops:
:oops:
 
Upvote 0
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.:)
 
Upvote 0
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.


Code:
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
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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 (y)
 
Upvote 0
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.

Code:
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
 
Upvote 0
The goto is great, and obviously classier than mine. :cool:

As things stand the code doesn't play well with split windows (e.g. the lower right one will fail reliably, whatever that means :rolleyes: ) . 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.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top