Delete macro

[Buns1976]

Hi Everyone,

I have been running a delete macro on a sheet we import where the first 10 rows, Column C:K have been static however I needed to change
the import sheet's structure. Unfortunately the rows I need to delete are no longer static so I'm wondering if I can "Delete by Criteria" instead?
It would need to delete anything in Column C that does not begin with "00" down to row 500. Below is the macro we used previously so the
top line(Range("C2:L10").Select) would need to be edited.

Thank you!

Sub DeleteRows()
'


'
    Range("C2:L10").Select
    Selection.ClearContents
    Columns("I:K").Select
    Selection.Delete Shift:=xlToLeft
End Sub

 

[Joe4]

Try this:

Sub DeleteRowMacro()

    Dim myRange As Range
   
    Application.DisplayAlerts = False

'   Set range to apply to
    Set myRange = Range("C1:L500")

'   Filter to hide all rows not starting with "00"
    myRange.AutoFilter Field:=1, Criteria1:="<>00*", Operator:=xlAnd
        
'   Delete all visible rows
    myRange.Offset(1, 0).Resize(myRange.Rows.Count - 1).Rows.Delete

'   Remove filters
    myRange.AutoFilter
    
    Application.DisplayAlerts = True
    
End Sub

 

[Buns1976]

Thanks Joe4,

I may have not explained this correctly. The macro you presented does delete the rows BUT in looking at the original macro
we only want to delete by criteria in columns C:L and leave Colmns A:B as they are. Sorry about that!

Thanks

 

[Joe4]

Makes it a bit trickier, using filters. Couldn't get it to work, so went with a less efficient loop solution (for 500 rows, shouldn't be a big deal):

Sub DeleteRowMacro2()

    Dim r As Long
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

'   Loop through rows 2 - 500 backwards
    For r = 500 To 2 Step -1
'       See if column C doesn't start with "00"
        If Left(Cells(r, "C"), 2) <> "00" Then
'           Delete columns C-L
            Range(Cells(r, "C"), Cells(r, "L")).Delete Shift:=xlUp
        End If
    Next r
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

 

[Buns1976]

Thanks Joe4,

That did the trick!! Wondering if there is a way to keep the grid lines visible on the "Shift Up"?

Thanks

 

[Joe4]

Wondering if there is a way to keep the grid lines visible on the "Shift Up"?

Not sure what you mean?

 

[Buns1976]

Hi Joe4,

I got it and thanks so much for your help!!