Delete row based on A1 value

[jrisebo]

How do I delete a row based on A1 value? If A1 has a 0 in it, I want to delete that row. If it has a 1, I want to keep that row.

 

[mumps]

Is it only that first row or any row in column A?

 

[jrisebo]

Is it only that first row or any row in column A?

It could be any row in column A. Thanks

 

[JoshuaD]

There are two options.

Option One:

Use a for loop to loop through each cell in column A and check to see if the value = 0.
The code below is an example I threw together of this loop. I added a few features that makes it more dynamic in case your range fluctuates.

Sub Loope_De_Loop()
'
' Macro1 Macro
'

[FONT=Calibri][SIZE=3][COLOR=#000000]'[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim rng As Range
Dim c As Range
Dim UsedRng As Range
Dim LastRow As Long
Dim Startrow As Integer
Dim ColLetter As String[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Set UsedRng = ActiveSheet.UsedRange
   
    'Finds last used row for a dynamic range
   LastRow = UsedRng(UsedRng.Cells.Count).Row[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]
'Set which column to look at and which row to start at.
ColLetter = "A"
Startrow = 1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'Change to meet size
Set rng = Range(ColLetter & Startrow & ":" & ColLetter & LastRow)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'loops through each cell in range and deletes the whole row if cell value = 0
For Each c In rng[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    If c = 0 Then
    
        c.EntireRow.Delete
    
    End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Next c[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'Selects first cell for flawless transition
Range(ColLetter & Startrow).Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

Option Two:
Use a filter and remove code. I personally like this one better. It is faster and cleaner. It uses Excels built in Auto Filter and deletes the visible range. This one will only work if the data is in a range and not in a Table.

Sub Filter_And_Remove_Range()
'
'
Dim RngToDelete As Range
Dim UsedRng As Range
Dim FirstRow As Long, LastRow As Long, FirstCol As Long, LastCol As Long
Dim HdrVal As Long
Dim Vcol As Integer
Dim Crit As String

'Calculate Header Row
'Which row does the header start in? this will add the header value to the first row used. Only works when there is no data above the range.
HdrVal = 1
'Column number to look for Criteria
Vcol = 1
'Criteria to look for. For blanks use "="
Crit = 0



   Set UsedRng = ActiveSheet.UsedRange
   
   FirstRow = UsedRng(1).Row
   FirstCol = UsedRng(1).Column
   LastRow = UsedRng(UsedRng.Cells.Count).Row
   LastCol = UsedRng(UsedRng.Cells.Count).Column
    
On Error Resume Next
    
     Range(Cells(FirstRow + HdrVal, FirstCol), Cells(LastRow, LastCol)).Select
        ActiveSheet.Range(Cells(FirstRow + HdrVal, FirstCol), Cells(LastRow, LastCol)).AutoFilter Field:=1, Criteria1:=Crit
        Set RngToDelete = Selection.SpecialCells(xlCellTypeVisible)
        Selection.AutoFilter
        RngToDelete.Delete
        ActiveSheet.Range(Cells(HdrVal, FirstCol), Cells(LastRow, LastCol)).AutoFilter Field:=1
        Cells(FirstRow + HdrVal, FirstCol).Select
End Sub

Cheers!

 

[vcoolio]

Hello JRisebo,

Autofilter is always a good option:-

Sub Test()

Application.ScreenUpdating = False

With Sheet1.[A1].CurrentRegion
        .AutoFilter 1, 0
        .Offset(1).EntireRow.Delete
        .AutoFilter
End With

Application.ScreenUpdating = True

End Sub

This assumes that your data starts in row2 with headings in row1.
I've used the sheet code (Sheet1) above rather than sheet name.


Or, if you don't have headings:-

Sub Test()

Application.ScreenUpdating = False

With Sheet1.[A1].CurrentRegion
        .AutoFilter 1, 0
        .SpecialCells(12).EntireRow.Delete
End With

Sheet1.AutoFilterMode = False
Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[Michael M]

you could also use....which is faster...no loop !!

Sub MM1()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Replace "0", "#N/A", xlWhole, , False, , False, False
    Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

 

[JoshuaD]

you could also use....which is faster...no loop !!

Sub MM1()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Replace "0", "#N/A", xlWhole, , False, , False, False
    Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

I like this code. I haven't seen it that way before. Thank you for sharing!

 

[hiker95]

And, another variation.

Sub DeleteRowIfColumnA0()
' hiker95, 02/28/2019, jrisebo, ME1089105
Application.ScreenUpdating = False
Columns("A").Replace 0, "#N/A", xlWhole
Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub