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

Macro code

J

James Carlo Cruz

Member
Hi,

Would like to ask if anyone can shortened my code for filtering and deleting a data. I only wanted to delete all visible cells except header after filtering
I have attached a notepad wherein I put my code,

Here is one of them :

Code: 
Worksheets(2).Activate

If ActiveSheet.FilterMode = True Then
             ActiveSheet.ShowAllData
             ActiveSheet.AutoFilterMode = False
End If
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("A1").AutoFilter Field:=10, Criteria1:="*svc*"

lr = Cells(Rows.Count, 1).End(xlUp).Row

        If lr > 1 Then
                Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End If
Range("A1").Select
Selection.AutoFilter
 

Attachments

  • macro for filter.txt
    2.8 KB · Views: 1
Last edited by a moderator:
pls try:

Code: 
Worksheets(2).Activate

If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
ActiveSheet.AutoFilterMode = False
End If
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("A1").AutoFilter Field:=10, Criteria1:="*svc*"

lr = Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Range("$A$1:$I$"& lr).Offset(1,0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete

Range("A1").Select
Selection.AutoFilter
 
Hi Hui,

This is very helpful, my code was running much faster now.
Would like to ask a follow up question : I could not save the excel file in ".xlsb" format is there something wrong with this code :

Code: 
Sub save()

ThisWorkbook.save

Sheets(Array("Raw Data")).Copy
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Done\" & Format(Now(), "DD MMM YY") & " - Final Data for Order Volume" & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Application.DisplayAlerts = True

MsgBox "Done Saving a copy in the Done Folder", vbInformation, " HP reports analyst"
MsgBox "Data scrubbing template will now close", vbInformation, " HP reports analyst"
 
Last edited by a moderator:
Hi Hui,

I already figure out my error : FileFormat:=xlOpenXMLWorkbook should be change to FileFormat: xlExcel12 if Iwant to save a file with .xlsb format
 
You must log in or register to reply here.
Back
Top