Deleting cells on a certain condition

[marino3d]

Hi,

I'm looking to create a macro where someone could enter a column and a value and all rows that contain that value in that column will be erased. I was browsing old posts on this board and I found something that looks good but has one fatal error. Here's the code:

Sub myDeleteRows()

Dim MyCol As String
Dim MyVal As Variant
Dim i As Integer

MyCol = InputBox("column to look through", "Column Search", "A")
MyVal = InputBox("Value to look for", "search value", 0)
  For i = 1 To Range(MyCol & "65536").End(xlUp).Row Step 1
    If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), MyVal) > 0 Then
    Range("A" & i).EntireRow.Delete
    End If
  Next i
  
End Sub

This works well, unless my value is 0 which comes up a lot. If I enter say column V and value 0 It deletes rows at random instead of just the rows that contain 0. That is not good. Any ideas how to fix this?

I am running Windows 7 and Office 2010.

 

[VoG]

Try

Sub myDeleteRows()

Dim MyCol As String
Dim MyVal As Variant
Dim i As Integer

MyCol = InputBox("column to look through", "Column Search", "A")
MyVal = InputBox("Value to look for", "search value", 0)
  For i = Range(MyCol & Rows.Count).End(xlUp).Row To 1 Step -1
    If Cells(i, MyCol) = MyVal Then Rows(i).Delete
  Next i
End Sub

 

[JamesW]

Hi there,

You almost have it, but you need to step backwards (bottom to top).

This is because say row 11 and 12 contain a 0, when you delete row 11, row 12 now becomes row 11 so it skips it.

Sub myDeleteRows()

Dim MyCol As String
Dim MyVal As Variant
Dim i As Integer

MyCol = InputBox("column to look through", "Column Search", "A")
MyVal = InputBox("Value to look for", "search value", 0)
  For i = Range(MyCol & "65536").End(xlUp).Row To 1 Step -1
    If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), MyVal) > 0 Then
    Range("A" & i).EntireRow.Delete
    End If
  Next i
  
End Sub

 

[JackDanIce]

Why not just filter for that particular item in that particular row and then delete the visible cells (except the header row)? Depending on the amount of data you have, that would be a more efficient/faster way to achieve the same end result

 

[JackDanIce]

This is some code I have in my personal macro book that deletes visible rows (but keeps the header), I manually apply the filter first though:

Sub DeleteVisibleRows()
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
ActiveSheet.AutoFilterMode = False
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[marino3d]

Hi there,

You almost have it, but you need to step backwards (bottom to top).

This is because say row 11 and 12 contain a 0, when you delete row 11, row 12 now becomes row 11 so it skips it.

Sub myDeleteRows()

Dim MyCol As String
Dim MyVal As Variant
Dim i As Integer

MyCol = InputBox("column to look through", "Column Search", "A")
MyVal = InputBox("Value to look for", "search value", 0)
  For i = Range(MyCol & "65536").End(xlUp).Row To 1 Step -1
    If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), MyVal) > 0 Then
    Range("A" & i).EntireRow.Delete
    End If
  Next i
  
End Sub

So far I tried this one and it totally erased all rows when I put 0 as a value. Luckily it was a test spreadsheet so no harm done . I'm going to try the other suggestions now and see what happens.

 

[JamesW]

It worked fine for me!

 

[marino3d]

It worked fine for me!

Really?

I had a spreadsheet filled with Data. Made one of the rows have a 0 value in column V. When selecting column V and setting the value to 0 it cleared out everything. Hmmmm. Ok let me try again with a different spreadsheet.

edit: Same thing. It works perfectly for any value but if I want to delete a row that contains a 0 in it everything in the spreadsheet gets erased. What could it be? I have varying data from column A-AP and it doesn't like the number 0 for some reason.

 

[JackDanIce]

Marino,

Put a filter on your data. Filter to show the value 0, then try my code to delete just the visible rows.

 

[marino3d]

Marino,

Put a filter on your data. Filter to show the value 0, then try my code to delete just the visible rows.

JackDaniels,

Thanks! That actually did the trick. I guess I wanted to be lazy and resolve the issue with the click of one simple button but your method works too and is fast. So it works for any filtered data I assume?

Edit: The only thing I'm worried about when it comes to this and even Jame's solution is that if the user does this wrong everything gets erased. They may panic and do something dumb like save the spreadsheet. I want to have a button that starts your macro but if the data is not filtered everything gets erased. I dunno if it's worth having something that can accidentally delete everything to just save 10 minutes.