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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 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.

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 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.

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 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
  •