Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Select filtered data using VBA code

  1. #1
    Board Regular savindrasingh's Avatar
    Join Date
    Sep 2009
    Location
    Hyderabad, India
    Posts
    183
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Select filtered data using VBA code

    I am looking for the code to select the visible data after applying a data filter. Actually I know how to select the data after applying the data filter but the issue is I am not able to exclude the header row and give the target range as used (non-blank) rows only!!

    I am using below code to Select the Visible rows in the target range:

    Code:
    Range("A:p").SpecialCells(xlCellTypeVisible).Select
    Problems in this code are:

    1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range.

    2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.

    E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6

    3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection.

    Some one please revert with the solution.
    Thanks in advance.
    Savindra Singh
    Sr. Tech Specialist,
    Wells Fargo IS
    Hyderabad (India)

    The wisest mind has something yet to learn

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Select filtered data using VBA code

    Try

    Code:
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(.xlUp).Row
    Range("A2:P" & LR).SpecialCells(xlCellTypeVisible).Select
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular savindrasingh's Avatar
    Join Date
    Sep 2009
    Location
    Hyderabad, India
    Posts
    183
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select filtered data using VBA code

    Thanks Peter,

    Its working. I understood that how you are calculating the last used row in the given ares but I didn't understand the second part. Could you please help me to understand this part:
    Code:
    Range("A2:P" & LR).SpecialCells(xlCellTypeVisible).Select
    Since last time when I tried to Use:

    Code:
    Range("A2:P").SpecialCells(xlCellTypeVisible).Select
    it was giving below error:

    Error '1004'
    Application defined or object defined error.

    (I would like to appreciate you for your quick and correct reply on many posts I had seen. I can't believe someone can reply so fast and correctly on so many threads. I am really impressed)
    Savindra Singh
    Sr. Tech Specialist,
    Wells Fargo IS
    Hyderabad (India)

    The wisest mind has something yet to learn

  4. #4
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Select filtered data using VBA code

    Hi and thank you for your kind words.

    If you use

    Code:
    Range("A:P").Select
    all of columns A to P will be selected.

    If you try

    Code:
    Range("A2:P").Select
    it will generate an error since, as you specified a starting row (2), Excel expects an end row.

    I hope that this is clear - if it isn't please post back.
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    Board Regular savindrasingh's Avatar
    Join Date
    Sep 2009
    Location
    Hyderabad, India
    Posts
    183
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select filtered data using VBA code

    Now I understood.. Thanks for the explanation.
    Savindra Singh
    Sr. Tech Specialist,
    Wells Fargo IS
    Hyderabad (India)

    The wisest mind has something yet to learn

  6. #6
    New Member
    Join Date
    Jan 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select filtered data using VBA code

    Thank you SO MUCH for sharing this little bit of code. I can't tell you how many times I have used it in various forms at work. Simple, straightforward and it works beautifully! Thank you again for sharing your knowledge.

  7. #7
    New Member
    Join Date
    May 2017
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select filtered data using VBA code

    Dear Folks,


    I know, I am bit late in this thread.
    I tried to follow Mr. MVP's code

    Code:
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(.xlUp).Row
    Range("A2:P" & LR).SpecialCells(xlCellTypeVisible).Select

    I encountered an error for this area.
    .End(.xlUp).Row

    The Error is
    Compile Error:
    Invalid or unqualified reference


    Kindly help !!!
    Last edited by Mushtaq86; May 14th, 2017 at 07:31 AM.

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    9,391
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Select filtered data using VBA code

    I don't know why VoG had it in there originally other than just a typo, but there shouldn't be a period before .xlUp.
    Should be...

    Code:
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    New Member
    Join Date
    May 2017
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select filtered data using VBA code

    Mark,

    I am just a rookie in this and You really made it for me.
    Loads of respect & thanks.

    Thanks to VoG as well.
    Wish you all loads of success.

    Cheers !!!

User Tag List

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