VBA to Delete Rows based on value of a cell

[Aggie2014]

Hi all,

As part of a bigger macro, I'm adding code that will delete rows of data if the value in column O is 0 and I can't seem to get it to function correctly. Data in column O is a dummy variable that's been created based on data in other columns and is posted as a value, rather than formula. I'm not getting any errors when I run the macro, however the cells with 0 value in column O do not get deleted.

The code that I'm using for the deletion is as follows:

For w = Range("C" & Rows.Count).End(xlUp).Row to 7 Step -1
If Range("O7" & w).Value = "0" Then
Rows(w).EntireRow.Delete
End If
Next w

Am I not seeing something that's causing it not to work properly?

 

[Trebor76]

Hi Aggie2014,

Try this:

Option Explicit
Sub Macro1()
    
    Dim w As Long
    
    Application.ScreenUpdating = False

    For w = Range("O" & Rows.Count).End(xlUp).Row To 7 Step -1
        If IsNumeric(Range("O" & w)) = True And Val(Range("O" & w)) = 0 Then
            Rows(w).EntireRow.Delete
        End If
    Next w
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[Michael M]

Maybe this way....

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

 

[Trebor76]

Hi Michael,

Hope you're well. Happy new year!!

Nice code - the only "issue" is that cells that have zero as the result of a formula (say =6-6) will not be deleted. Thus may not be an issue of course.

Hope you enjoy the Elvis festival in just over a week

Cheers,

Robert

 

[Peter_SSs]

the only "issue" is that cells that have zero as the result of a formula (say =6-6) will not be deleted.

Not the only issue, as
- rows with cells that already contained a constant #N/A value would also be deleted,
- rows above row 7 could also be deleted
but these also ..

may not be an issue of course.

BTW, @Trebor76 & @Aggie2014, since Rows(w) is already an entire row, that does not need to be re-specified.

Rows(w).EntireRow.Delete

@Aggie2014

1. About how big (number of rows) is your original data set?
2. Can cells in column O be empty or contain "" and, if so, should they be deleted too?

 

[Michael M]

Hmm.
I read the post to be...

and is posted as a value, rather than formula

 

[DanteAmor]

Here is another macro for you to consider:

Sub Macro1()
  Range("A1:O" & Range("O" & Rows.Count).End(xlUp).Row).AutoFilter Field:=15, Criteria1:=0
  ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
  ActiveSheet.ShowAllData
End Sub

 

[Aggie2014]

Not the only issue, as
- rows with cells that already contained a constant #N/A value would also be deleted,
- rows above row 7 could also be deleted
but these also ..

BTW, @Trebor76 & @Aggie2014, since Rows(w) is already an entire row, that does not need to be re-specified.

Rows(w).EntireRow.Delete

@Aggie2014

1. About how big (number of rows) is your original data set?
2. Can cells in column O be empty or contain "" and, if so, should they be deleted too?

@Peter_SSs

This dataset is a financial line item summary separated by categories i.e. revenues, supplies, capital outlay, etc, so the number of lines will vary from around 15 to over a 100 depending on the fund. Because they are separated by the categories and have blank rows between each of the subsets deleting lines that have blanks would be an issue. The dummy variable that's in column N pulls out the rows that have 0 in several other columns, through a sum function and then posted as values, and thus are extraneous to the analysis. That's been the bit I'm struggling with because I need the rows that are blank to remain in place.

 

[Aggie2014]

Here is another macro for you to consider:

Sub Macro1()
  Range("A1:O" & Range("O" & Rows.Count).End(xlUp).Row).AutoFilter Field:=15, Criteria1:=0
  ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
  ActiveSheet.ShowAllData
End Sub

@DanteAmor

Thanks for the code! I've tried using autofiller, but because I have blank rows separating my data subsets, the filter would not go all the way to the bottom of the dataset, but stop at the first blank separator row. Could the proposed code be augmented to go to the bottom of the set, rather than stopping at the first blank?

 

[Aggie2014]

Maybe this way....

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

@Michael M

Thanks! This is precisely what I needed! I dropped it into my macro and it worked like a charm