Results 1 to 10 of 10

Thread: Using Offset on Visible cells only
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2017
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using Offset on Visible cells only

    Hi,

    I have the code

    Code:
    ActiveSheet.Range("A10").Offset(1, 0).Select
    But this does not work for me as my data is filtered. I do not want to unfiltered the data. But how can I use offset on functioning with on the cells that are visible ?

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,913
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Using Offset on Visible cells only

    this method works

    Code:
        With Range("A10")
            Range(.Offset(1), Cells(Rows.Count, .Column)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
        End With

  3. #3
    Board Regular
    Join Date
    Mar 2017
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Offset on Visible cells only

    Hi ,

    I seem to get the code 'Application defined or object defined error' when I run the code below.

    Quote Originally Posted by Yongle View Post
    this method works

    Code:
        With Range("A10")
            Range(.Offset(1), Cells(Rows.Count, .Column)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
        End With

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,913
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Using Offset on Visible cells only

    Try this then
    - it also works for me

    Code:
        Dim cel As Range
        Set cel = ActiveSheet.Range("A10")
        Range(cel.Offset(1), cel.Parent.Cells(Rows.Count, cel.Column)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
    Last edited by Yongle; Aug 5th, 2019 at 06:48 AM.

  5. #5
    Board Regular
    Join Date
    Mar 2017
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Offset on Visible cells only

    Unfortunately , this does not work for me. once the code is run it stays at the cell below Range("A10")

    Quote Originally Posted by Yongle View Post
    Try this then
    - it also works for me

    Code:
        Dim cel As Range
        Set cel = ActiveSheet.Range("A10")
        Range(cel.Offset(1), cel.Parent.Cells(Rows.Count, cel.Column)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,341
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Using Offset on Visible cells only

    How is your data being filtered?
    If that code puts you on A11 is that row hidden?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,604
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Using Offset on Visible cells only

    There is the brute force approch

    Code:
    Dim myCell as Range
    
    Range("A10").Select
    
    Set myCell = ActiveCell
    Do
        Set myCell = myCell.Offset(1, 0)
    Loop Until (myCell.EntireRow.Hidden = False) 
    MyCell.Select
    Last edited by mikerickson; Aug 5th, 2019 at 09:47 AM.

  8. #8
    Board Regular
    Join Date
    Mar 2017
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Offset on Visible cells only

    No the code works only for the first offset for the first visible cell. i.e ( It will drop down to range(A19).

    - Data filtered showing all data that has a 'Y' associated to it in a hidden column.

    Quote Originally Posted by Fluff View Post
    How is your data being filtered?
    If that code puts you on A11 is that row hidden?

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,913
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Using Offset on Visible cells only

    You asked for a "single" result.
    You did not mention anything about the "first" or "next" filtered cell. The code I gave you was for one action, not a loop. But you appear to be attempting some sort of loop.

    What exactly are you trying to achieve ?

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,341
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Using Offset on Visible cells only

    In that case you need to explain what you are trying to do, as the code that Yongle posted in post#4 is selecting the first visible cell below A10, which is what your code was trying to do.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •