Deleting Rows with a specific Value

[AlexMach]

Hi All,

I have a problem trying to delete multiple rows with the value 0.

I have encode specific rows with either a 0 or 1 whereby the rows with 1's are needed though the ones in between that may have a 0 need deleting (entire row). The the entire data range does not exceed 360 as it's always a full year of data.

This is what I have

Public Sub Delete1()
Dim cell As Range
Range("ad2:ad360").Select
For Each cell In Selection
If cell.Value = "0" Then
cell.EntireRow.Delete
End If
Next cell
Range("ad2").Select
End Sub

the problem is that it is not deleting rows and getting stuck.

Would anyone have a solution to my problem? I would really appreciate any input here.

thanks in advance

 

[Useful]

Hi! you may see it here http://www.mrexcel.com/forum/showthread.php?t=550847

 

[chuckles1066]

The good people here found me a solution to something very similar:

http://www.mrexcel.com/forum/showthread.php?t=228906

 

[Useful]

Public Sub Delete2()
Application.ScreenUpdating = False
Dim cell As Range
Range("ad2:ad360").Select
For Each cell In Selection
If cell.Value = "0" Then
cell.EntireRow.Delete
End If
Next cell
On Error Resume Next
 Call Delete2
Range("ad2").Select
End Sub

 

[John Davis]

Hi All,

I have a problem trying to delete multiple rows with the value 0.

I have encode specific rows with either a 0 or 1 whereby the rows with 1's are needed though the ones in between that may have a 0 need deleting (entire row). The the entire data range does not exceed 360 as it's always a full year of data.

This is what I have

Public Sub Delete1()
Dim cell As Range
Range("ad2:ad360").Select
For Each cell In Selection
If cell.Value = "0" Then
cell.EntireRow.Delete
End If
Next cell
Range("ad2").Select
End Sub

the problem is that it is not deleting rows and getting stuck.

Would anyone have a solution to my problem? I would really appreciate any input here.

thanks in advance

Try making this change:

Public Sub Delete1()
Dim cell As Range
Range("ad2:ad360").Select
For Each cell In Selection
If cell.Value = 0 Then      <--------
cell.EntireRow.Delete
End If
Next cell
Range("ad2").Select
End Sub

 

[Useful]

Try making this change:

Public Sub Delete1()
Dim cell As Range
Range("ad2:ad360").Select
For Each cell In Selection
If cell.Value = 0 Then      <--------
cell.EntireRow.Delete
End If
Next cell
Range("ad2").Select
End Sub

Hi! John Davis your code isn't correct erik.van.geit was right visit here http://www.mrexcel.com/forum/showthread.php?t=550847

 

[John Davis]

Hi! John Davis your code isn't correct erik.van.geit was right visit here http://www.mrexcel.com/forum/showthread.php?t=550847

Thanks for pointing that out. I only made a modification too the ops code. I certainly wouldn't argue with Erik or Richard. However, when I delete rows for particular criteria I always use autofilter a loopless approach:

Sub AlexMach()

With Range("AD1:AD360")

    .AutoFilter Field:=1, Criteria1:="0"
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete Shift:=xlUp
    .AutoFilter
    
End With

    Columns("AD:AD").AutoFilter
    
End Sub