• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help to adjust macro in advanced filter

R

RAM72

Member
Recorded advanced filter macro from column H to J of unique values copied to range column AC to AE of same sheet named workings , this same range is then cut to another sheet named summary report in same workbook.

Can anyone one help to adjust vba macro in a more elegant way with some modifcations in red ***( )***to last row of data

Thanks

Code: 
Sub testtestunique()
'
' testtestunique Macro
'

'
Range("H1:J629").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"AC1"), Unique:=True **(up to last data in column)**

Columns("AC:AC").EntireColumn.AutoFit
Columns("AD:AD").EntireColumn.AutoFit
Columns("AE:AE").EntireColumn.AutoFit
Range("AC1:AE1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut **(up to last data in column)**
Sheets("Summary Report ").Select
Range("A1").Select
ActiveSheet.Paste ***(Autofit columns**)

End Sub
 
Something like
Code: 
Sub test()
    With Sheets("summary").Cells(1)
        Range("H1:J629").AdvancedFilter 2, , .Cells, 1
        .CurrentRegion.EntireColumn.AutoFit
    End With
End Sub
 
jindon said:
Something like
Code: 
Sub test()
    With Sheets("summary").Cells(1)
        Range("H1:J629").AdvancedFilter 2, , .Cells, 1
        .CurrentRegion.EntireColumn.AutoFit
    End With
End Sub
Click to expand...

Tested it works well ,but I noticed that when I am on summary sheet It did not work properly or sometimes gives me unexpected results.

So I should always be on active sheet workings for the macro to works.

Is it possible to adjust on whatever sheet I am in the workbook, the macro works

Thanks if you couls adjust.
 
You can count rows via code.

Code: 
Dim lRow As Integer

'Count last row with data
lRow = Sheets("YourSheetNameHere").Range("H" & Rows.Count).End(xlUp).Row

Sheets("YourSheetNameHere").Range("H1:J" & lRow).AdvancedFilter
 
Code: 
Sub test()
    With Sheets("Summary Report ").Cells(1).CurrentRegion
        Sheets("workings").Cells(1).CurrentRegion.AdvancedFilter 2, , .Cells, 1
        .CurrentRegion.EntireColumn.AutoFit
    End With
End Sub
 

Attachments

  • test adbvanced filtercopu_test.xlsm
    135.5 KB · Views: 8
You must log in or register to reply here.
Back
Top