I use the following code to remove buildings in my property that are less than 33. So building 9-32 would be removed from the spreadsheet.
I would like to know how to modify this code so dates before today are also removed.
Any ideas?
Sub Remove_Gardens()
Dim i As Long, n As Long
Dim va, fm
' Remove garden units from this worksheet
Sheets("Make Ready Board").Activate
n = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A1:F" & n)
.Sort Key1:=[A1], Order1:=xlAscending, Header:=xlYes
va = .Value
For i = 2 To n
If CStr(Left(va(i, 1), 2)) >= "33" Then Exit For
Next
If i > 2 Then
Rows("2:" & i - 1).Delete
Else
MsgBox "Can't find Unit with number below 33xx"
End If
.Sort Key1:=[E1], Order1:=xlAscending, Header:=xlYes
fm = Application.Match(CLng(CDate(Date)), Range("E1:E" & n), 0)
If IsNumeric(fm) Then
If fm > 2 Then Rows("2:" & fm - 1).Delete
Else
fm = Application.Match(CLng(CDate(Date)), Range("E1:E" & n), 1)
If IsNumeric(fm) Then Rows("2:" & fm).Delete
End If
End With
End Sub
I would like to know how to modify this code so dates before today are also removed.
Any ideas?
Sub Remove_Gardens()
Dim i As Long, n As Long
Dim va, fm
' Remove garden units from this worksheet
Sheets("Make Ready Board").Activate
n = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A1:F" & n)
.Sort Key1:=[A1], Order1:=xlAscending, Header:=xlYes
va = .Value
For i = 2 To n
If CStr(Left(va(i, 1), 2)) >= "33" Then Exit For
Next
If i > 2 Then
Rows("2:" & i - 1).Delete
Else
MsgBox "Can't find Unit with number below 33xx"
End If
.Sort Key1:=[E1], Order1:=xlAscending, Header:=xlYes
fm = Application.Match(CLng(CDate(Date)), Range("E1:E" & n), 0)
If IsNumeric(fm) Then
If fm > 2 Then Rows("2:" & fm - 1).Delete
Else
fm = Application.Match(CLng(CDate(Date)), Range("E1:E" & n), 1)
If IsNumeric(fm) Then Rows("2:" & fm).Delete
End If
End With
End Sub