Log In
Register
Home
Active Member
January 23, 2019
Offline
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
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello,
I noticed that Jon Acampora just uploaded such example.
VIP
Trusted Members
June 25, 2016
Offline
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
Active Member
January 23, 2019
Offline
That worked great. Thank you so much Sunny!
VIP
Trusted Members
June 25, 2016
Offline
No problem.
Trusted Members
Moderators
November 1, 2018
Offline
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)
