Select filtered data using VBA code

savindrasingh

Board Regular
Joined
Sep 10, 2009
Messages
183
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

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

savindrasingh

Board Regular
Joined
Sep 10, 2009
Messages
183
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)
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

savindrasingh

Board Regular
Joined
Sep 10, 2009
Messages
183
Now I understood.. Thanks for the explanation. :)
 

millerprm

New Member
Joined
Jan 14, 2015
Messages
6
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.
 

Mushtaq86

New Member
Joined
May 7, 2017
Messages
22
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:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,684
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
 

Mushtaq86

New Member
Joined
May 7, 2017
Messages
22
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 !!!
 

mladenjevtic

New Member
Joined
May 23, 2018
Messages
12
Hello all... I know this is an old thread, but i wanted to check if there is a solution with one of my issues.
This code for selection is awesome, it does the job great!

What problem i have is that sometimes when i filter my table, there isn't any data to select and with this code it select the table header and change it to what it's supposed to be something for the selected table data, not table headers.

Is there a way around it? I still need this code to select data range if there is any when i'm filtering rows, but if the values do not exist in the table i just need to skip it.
 

Forum statistics

Threads
1,181,649
Messages
5,931,217
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top