Select filtered data using VBA code

[savindrasingh]

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:

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.

 

[Akuini]

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.

Which code are you talking about?

 

[Akuini]

If the header in row 1 this code will stop (exiting sub) if there is no data:

lr = Range("A" & Rows.count).End(xlUp).Row
If lr < 2 Then Exit Sub
Range("A2:P" & lr).SpecialCells(xlCellTypeVisible).Select

 

[mladenjevtic]

Which code are you talking about?

Comment #2 .

But i thing i got it, through the counter with an if statement.

 

[AATU87]

Try

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

A total newbie here so I apologize in advance for some silly questions I migth have.

The code above works perfectly when the excel file in which the filtering is done is already open.
However, I'm trying to run a macro in which the excel file is opened automatically. In that case, only row "A" is selected if I use the code provided.

Sorry if the post is bit unclear.
I'm more than happy to provide more details if required.

Thanks a lot in advance.

 

[orange12345]

Thanks a lottt for this one!!!