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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Try

Code:
Dim LR As Long
LR = Range("A" & Rows.Count).End(.xlUp).Row
Range("A2:P" & LR).SpecialCells(xlCellTypeVisible).Select
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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 !!!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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
Back
Top