Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting the next visible cell (Excel 2002)

    How can I tell my macro to select the next visible cell? I've applied a filter and want to move from the header cell to the first visible row. I've tried Activecell.Offset(1,0).select but this selects the next hidden row?! Thanks, Andy.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Selecting the next visible cell (Excel 2002)

    You could use something like this:

    Dim oCell As Range
    Set oCell = ActiveCell
    Do
    Set oCell = oCell.Offset(1, 0)
    Loop Until oCell.Height > 0
    oCell.Select

    Note: in most macros, it is not really necessary to select cells. You can retrieve and set their value, format them, etc. without selecting them.

  4. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting the next visible cell (Excel 2002)

    The full story: I've applied a filter to a list and want to add a data series to a chart for each visible row that remains - actually a bubble to a bubble graph..
    Is there not a way that I can use GoTo/ Special / Visible Cells Only within my code to move between the visible cells? Although you're suggested method would work, it seems inefficient when I have many rows to explore. Thanks again, Andy.

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Selecting the next visible cell (Excel 2002)

    1) It would have helped if you had made that clear initially.
    2) You're making things much too difficult. If you create a chart based on the entire range, it will automatically display only the visible values when the range is filtered. No code needed.

  6. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting the next visible cell (Excel 2002)

    There are two hundred rows initially and each row is created as a separate series for a Bubble Graph. I only need to create the series for whichever filtered data are showing when they choose to run a macro. Creating a graph initially for the full set of records would be slow/cumbersome and hopefully unnecessary..

  7. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Selecting the next visible cell (Excel 2002)

    Perhaps you can use something like Range("A1").CurrentRegion.SpecialCells(xlCellTypeV isible)

Posting Permissions

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