Delete Specific Rows Using VBA

[Timbo]

I am working on a worksheet with dates in column G starting in G2 th
last under the last row containign a date has " " in it.

The dates are formatted dd/mm/yyyy.

I need an input box which prompts me for a date and then formats th
reply in the same way dd/mm/yyyy.

I then want the macro to run down row G until it reaches the " " an
delete any row with a date before the date enterred into the input box.

All and any help much appreciated

 

[jasontferrell]

If the regional settings of your computer are set for dd/mm/yyyy
format, then Excel should be able to convert the string returned by
the inputbox correctly. Try this, but you should clean up the
"activesheet" references so that users cannot change the active sheet
mid-process and end up with an unexpected result.

Public Sub DeleteRows()
Dim sDate As String, lRow As Long, lCount As Long
sDate = InputBox("Please enter the date")
If IsDate(sDate) Then
lRow = 2
For lCount = 2 To ActiveSheet.UsedRange.Rows.Count
If ActiveSheet.Cells(lRow, 7).Value < DateValue(sDate)
Then
ActiveSheet.Cells(lRow, 7).EntireRow.Delete
Else
lRow = lRow + 1
End If
Next lCount
Else
MsgBox sDate & " does not appear to be a valid date"
End If
End Sub

 

[JLGWhiz]

If there are no dates below your " ", then then this should work. You
will need to change resp to whatever variable you are using on the input
box.

Sub something()
lr = ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row
Set rng = ActiveSheet.Range("G2:G" & lr)
resp = InputBox("enter date", "Date")
dt = Format(resp, "dd/mm/yyyy")
For i = lr To 2 Step - 1
If ActiveSheet.Cells(i, 7) < dt Then
ActiveSheet.Cells(i, 7).EntireRow.Delete
End If
Next
End Sub

 

[JLGWhiz]

You might need to change this line.

dt = Format(resp, "dd/mm/yyyy")

To:

dt = CDate(Format(resp, "dd/mm/yyyy"))

if column G is formatted as Date.