PDA

View Full Version : [SOLVED] Delete row based on date and cell value



Barryj
12-30-2015, 08:54 PM
I am wanting to delete rows that are greater than 30 days and based on the value "NO" in a cell. I have the date in column A and the Value "NO" will be in column C.

I have this macro to delete rows greater than 30 days but I need to add to it that only rows with NO in them will be deleted.


Sub DeleteCells()
Dim LR As Long, I As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
For I = LR To 1 Step -1
If Range("A" & I).Value <= Date - 30 Then Rows(I).Delete
Next I
Application.ScreenUpdating = True
End Sub


Thanks for any assistance

SamT
12-30-2015, 09:33 PM
...-30 And UCase(Range("C" & I)) = "NO" Then

Barryj
12-30-2015, 11:00 PM
Thanks SamT works perfect
I will mark as solved
Have a great New year

mancubus
12-31-2015, 05:28 AM
as an alternative, i regularly use autofilter method with large data sets.



Sub vbax_54682_Del_Rows_Based_on_Two_Cols()
With Worksheets("MySheet") 'change MySheet to suit
.Cells(1).AutoFilter Field:=1, Criteria1:="<=" & (Date - 30)
.Cells(1).AutoFilter Field:=3, Criteria1:="=NO"
.UsedRange.Columns(1).Offset(1).SpecialCells(12).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub

SamT
12-31-2015, 09:15 AM
Why are you invoking the Column Object?

.UsedRange.Columns(1).Offset(1).SpecialCells(12).EntireRow.Delete

SamT
Fellow ID Follower

mancubus
01-01-2016, 02:16 AM
you may assume i am too lazy to amend the adopted code if it produces the same result. :rofl:
:banghead: :crying::devil2: