How to copy visible cells after filter in VBClosed

Question

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 



Configuration: Windows XP / Internet Explorer 6.0

rizvisa1 · Answer

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

Lavanya · Answer

updated the question

rizvisa1 · Answer

Now it is confusing.  What you mean by filter the visible cells after autofiler

Lavanya · Answer

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.

rizvisa1 · Answer

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.

Lavanya · Answer

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 ,

Lavanya · Answer

In the above macro after the step Range("C6154").Select , it is Selection.Paste

rizvisa1 · Answer

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.

Lavanya · Answer

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 ,

rizvisa1 · Answer

I am not looking for actual data, but a sample data. 
I am still not sure what is the significance of Range("B6154").Select

Lavanya · Answer

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

Lavanya · Answer

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 ....

rizvisa1 · Answer

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

Lavanya · Answer

Hello rizvisa ,

I have sent you a mail with attachment ...Kindly look into that along with the macro

rizvisa1 · Answer

I dont have any thing in the email

Lavanya · Answer

now???!!!!

rizvisa1 · Answer

nope

Lavanya · Answer

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

rizvisa1 · Answer

Nope. Where have you been sending to ?

How to copy visible cells after filter in VB

19 responses

Similar discussions

Subscribe To Our Newsletter!