VBA delete rows based on cell value

[anelem]

Hi
I have one worksheet where I create text boxes and assign macros in the workbook.
I am trying to create VBA to delete the row on a Worksheet named "Register" if the value of the cell in Column 16 = 0 (Zero) - so example value in Cell P4 = 0, therefore row 4 should be deleted.
The number of rows can vary with a max of 2,000 lines
Really would appreciate some help.
Thank you

 

[Snakehips]

@anelem Give this a try.

Sub Del_P()
With Sheets("Register")
LastP = .Range("P" & Rows.Count).End(xlUp).row
Application.ScreenUpdating = False
    For r = LastP To 2 Step -1
        If .Cells(r, "P") = 0 Then .Cells(r, "P").EntireRow.Delete
    Next r
End With
Application.ScreenUpdating = True
End Sub

 

[Joe4]

Here is a way to do it without using loops (so it may be a little faster if you have a lot of data):

Sub MyDeleteMacro()
'
    Dim lr As Long, lr2 As Long

    Application.ScreenUpdating = False

'   Find last row with data in column P
    lr = Cells(Rows.Count, "P").End(xlUp).Row
    
'   Hide all rows not equal to zero
    Columns("P:P").AutoFilter
    ActiveSheet.Range("$P$1:$P$" & lr).AutoFilter Field:=1, Criteria1:="0"
    
'   Find last row in column P with data after filter
    lr2 = Cells(Rows.Count, "P").End(xlUp).Row

'   Exit sub if no data to delete data (only header visible)
    If lr2 = 2 Then Exit Sub

'   Delete unhidden data
    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True

'   Remove filter
    Range("P1").AutoFilter
    
    Application.ScreenUpdating = True
    
End Sub

 

[anelem]

@anelem Give this a try.

Sub Del_P()
With Sheets("Register")
LastP = .Range("P" & Rows.Count).End(xlUp).row
Application.ScreenUpdating = False
    For r = LastP To 2 Step -1
        If .Cells(r, "P") = 0 Then .Cells(r, "P").EntireRow.Delete
    Next r
End With
Application.ScreenUpdating = True
End Sub

Awesome, thanks for your quick response - this works perfectly!
Appreciate the help.

 

[rossross]

Here is a way to do it without using loops (so it may be a little faster if you have a lot of data):

Sub MyDeleteMacro()
'
    Dim lr As Long, lr2 As Long

    Application.ScreenUpdating = False

'   Find last row with data in column P
    lr = Cells(Rows.Count, "P").End(xlUp).Row
   
'   Hide all rows not equal to zero
    Columns("P:P").AutoFilter
    ActiveSheet.Range("$P$1:$P$" & lr).AutoFilter Field:=1, Criteria1:="0"
   
'   Find last row in column P with data after filter
    lr2 = Cells(Rows.Count, "P").End(xlUp).Row

'   Exit sub if no data to delete data (only header visible)
    If lr2 = 2 Then Exit Sub

'   Delete unhidden data
    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True

'   Remove filter
    Range("P1").AutoFilter
   
    Application.ScreenUpdating = True
   
End Sub

i'm aware this is a very old post but maybe you'll see and respond. if i'm using this and identifying which column to use, i'm using "ecol" (end column) which is dynamic. for the life of me, i cannot workout the syntax on how to get this written correctly. the dynamic rows works but i can't properly write how to use my end column.

 

[agnes88fe]

@anelem Give this a try.

Sub Del_P()
With Sheets("Register")
LastP = .Range("P" & Rows.Count).End(xlUp).row
Application.ScreenUpdating = False
    For r = LastP To 2 Step -1
        If .Cells(r, "P") = 0 Then .Cells(r, "P").EntireRow.Delete
    Next r
End With
Application.ScreenUpdating = True
End Sub

I know it's an older post, but it works wonderfully on my sheet as well, thank you so much!