
Active Member

January 23, 2019

I've never done a VBA or macro before. This would help me alot if somebody can help with a formula
If cells in same row, columns C,D,E, and F all <1 , delete entire row
http://i36.photobucket.com/alb.....5tipon.png
in my example photo, row 2 and 4 would be deleted, row 3 and 5 would not

VIP

Trusted Members

December 7, 2016

Hello,
I noticed that Jon Acampora just uploaded such example.

VIP

Trusted Members

June 25, 2016

Hi Dane
Just in case the article by Jon Acampora is too overwhelming (since you are new to VBA/macros) you can give this a try
Sub DelRows()
Dim LastRow As Long
Dim i As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = LastRow To 1 Step -1
'Count how many values are less than 1
If Application.CountIf(Range("C" & i & ":F" & i), "<1") = 4 Then
'Delete entire row if count is equal to 4
Rows(i).Delete Shift:=xlUp
End If
Next
Application.ScreenUpdating = True
End Sub
Hope this helps.
Sunny

Answers Post

VIP

Trusted Members

June 25, 2016



Trusted Members
Moderators

November 1, 2018

Just for info, you could also use an autofilter for that:
Sub DelRowsLessThanOne()
Application.ScreenUpdating = False
With Range("A1").CurrentRegion
.AutoFilter field:=3, Criteria1:="<1"
.AutoFilter field:=4, Criteria1:="<1"
.AutoFilter field:=5, Criteria1:="<1"
.AutoFilter field:=6, Criteria1:="<1"
If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
.Offset(1).Columns(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End If
.AutoFilter
End With
End Sub
1 Guest(s)
