Results 1 to 10 of 10

VBA for visible range

This is a discussion on VBA for visible range within the Excel Questions forums, part of the Question Forums category; I'm looking for the row and column number, or number of rows and columns, that are visible in activewindow. Activewindow.visiblerange.row, ...

  1. #1
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default VBA for visible range

    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.
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    64,938

    Default

    How do they don't quite get it done?

    Have you tried Columns.Count and Rows.Count?

    Or even Address?
    If posting code please use code tags.

  3. #3
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default

    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.



    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    64,938

    Default

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

    PS You do realise that there might be quite a few cells visible in the active window.
    If posting code please use code tags.

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,982

    Default

    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

  6. #6
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default

    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!
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  7. #7
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,982

    Default

    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.

  8. #8
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default

    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
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  9. #9
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,982

    Default

    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

  10. #10
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default

    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.
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com