Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows with specific text | New Users to Excel | |||
Delete Rows Without Specific Text | Excel Worksheet Functions | |||
Macro to delete specific rows | Excel Programming | |||
Delete rows with specific text | Excel Programming | |||
Macro to delete specific rows | Excel Programming |