CODEWITHSUNDEEP
vbaexcel
kong1802
kong1802

Reputation: 11

VBA Code, but need to keep rows with no data

I am using VBA to delete rows that do not meet a certain criteria. The code is working, however, I can't figure out how to keep the blank rows separating the data. Below is the code I'm using. It works well for deleting what I want it to, however, it also is deleting the blank lines in between. 

Sub DeleteRows()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long
For i = Range("E" & Rows.Count).End(xlUp).Row To 1 Step -1
    If (Range("E" & i).Value > -5 And Range("E" & i).Value < 5) Then
         Range("E" & i).EntireRow.Delete
    Else
    If (Range("D" & i).Value > -500 And Range("D" & i).Value < 500) Then
    Range("D" & i).EntireRow.Delete
    End If
    End If
    Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Thanks!

Upvotes: 0

Views: 91

Answers (2)

BerticusMaximus
BerticusMaximus

Reputation: 725

Adding another And statement should do the trick for example you can use <> to say Does Not Equal.

If (Range("E" & i).Value > -5 And Range("E" & i).Value < 5) And Range("E" & i).Value <> "" Then

Upvotes: 0

Matt Cremeens
Matt Cremeens

Reputation: 5151

I think it should be enough to check for blank spaces and if a cell is blank, do not delete that row. Like this

Sub DeleteRows()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long
For i = Range("E" & Rows.Count).End(xlUp).Row To 1 Step -1
    If (Range("E" & i).Value > -5 And Range("E" & i).Value < 5 and Range("E" & i) <> "") Then
         Range("E" & i).EntireRow.Delete
    Else
        If (Range("D" & i).Value > -500 And Range("D" & i).Value < 500 and Range("D" & i) <> "") Then
            Range("D" & i).EntireRow.Delete
        End If
    End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Upvotes: 2

Related Questions