How to copy visible cells after filter in VB [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello Team ,

How to write a code in VB which will filter the visible cells after a autofilter has been applied .As my data range chnages with each report .Please help me



Related:

19 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Before you apply the filter, find out the max number of rows

Then apply the filter

Now copy for row to last row (as evaluated before)

Rows("2:" & lMaxRow).copy

row I am using as example, you can have a range

When you paste it will only paste visible rows
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3121 users have said thank you to us this month

to explain further ..I have applied a Filter to a worksheet in Excel. The rows are not shown in consecutive order. When I selected a cell, I want to write a code to move the seleted cell to the next non-hidden row .And then copy the data and fill into the adjacent cell.

Hope now it is clear ....
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3121 users have said thank you to us this month

updated the question
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Now it is confusing. What you mean by filter the visible cells after autofiler
I have applied a autofilter .. all I want to do now is to copy the visible cells under the autofilter criteria and then copy and past it to the corresponding adjust column .Can you help.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Then my initial answer still stands. All you have to do is to find the last used row of the sheet

' this is used to located the last row on the sheet
Set Cell = Cells.Find("*", Cells(1, "A"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

If Cell Is Nothing Then
lMaxRows = 0

Else
lMaxRows = Cell.Row
End If

Will give you last row used.

If you still have trouble, then use macro recorder,
1, start recorder
2. apply filter
3. copy the visible cells/rows/range
4. paste
5. stop macro

this would be specific for this one, you can then make it more generic
and if you still have trouble, please on some shared site like https://authentification.site upload and excel file with data AND YOUR RECORDED MACRO, and post the link back here.
Hello ,

My macro says the below :-

Selection.AutoFilter
ActiveSheet.Range("$B$2:$V$6607").AutoFilter Field:=1, Criteria1:= _
"STANDARD"
Range("B6154").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("C6154").Select

Now the data is dynamic .. so the range B6154 will not be the same .So I want to make it generic . Will you be able to help me out?

Thanks ,
In the above macro after the step Range("C6154").Select , it is Selection.Paste
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Why this Range("B6154").Select ?

Could you please on a shared site but the excel file with data and your macro. It will be easier for you to explain and me to understand as well test or code if needed.
Hello ,

Iam sorry I cannot share the data ... but I need your help in copying the visible cellss after the autofilter and then paste the data into the adjascent column .I guess you will have a clear idea of what I need but looking at the macro .

Selection.AutoFilter
ActiveSheet.Range("$B$2:$V$6607").AutoFilter Field:=1, Criteria1:= _
"STANDARD"
Range("B6154").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("C6154").Select
Selection.Paste

I need the above to be more generalised .. like I do not need the range to be B6154 as the data is dynamic and it will keep changing .

I hope you get it .

Thanks ,
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
I am not looking for actual data, but a sample data.
I am still not sure what is the significance of Range("B6154").Select
Hello ,

there is no significance for this range .....as the data used is huge ... the set criteria is usually at the end of the sheet . Please kindly look into this ....

Sorry for the trouble .
Thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
i am sorry it still is not. I you could create a sample book and then apply your marco and based upon on macro and sample book, if you could explain, I might be able to help in some way
Hello rizvisa ,

I have sent you a mail with attachment ...Kindly look into that along with the macro
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
I dont have any thing in the email
now???!!!!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
nope
I have sent you two links in the mail.... this is the last try .. literrally .. hope it works.


After autofilter I want to copy visible cells and paste it to the adjascent column in VB ....

Thank
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Nope. Where have you been sending to ?