Delete Specific Rows Using VBA
 

I am working on a worksheet with dates in column G starting in G2 the
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 the
reply in the same way dd/mm/yyyy.

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

All and any help much appreciated.


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=76632

Delete Specific Rows Using VBA
 

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

Delete Specific Rows Using VBA
 

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


"Timbo" wrote in message
...

I am working on a worksheet with dates in column G starting in G2 the
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 the
reply in the same way dd/mm/yyyy.

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

All and any help much appreciated.


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=76632

Delete Specific Rows Using VBA
 

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.


"Timbo" wrote in message
...

I am working on a worksheet with dates in column G starting in G2 the
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 the
reply in the same way dd/mm/yyyy.

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

All and any help much appreciated.


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=76632