Macro to delete rows with cells less than user-inputed time

[pilotom]

Hi all,

My knowledge of VBA is very limited. I need help in trying to make a
macro I have do an additional step. I have a column (D) in a worksheet
that is showing time values (2:30 PM, 3:15 PM, etc.). I need to code
the macro so a user can enter a time value and the macro will then
delete any rows in that worksheet in which the corresponding cell in
column D shows a time value LESS THAN the value entered by the user.
I've seen a couple of macros that delete rows matching a string in a
cell but nothing that deletes based on the cell value being less than.
I would appreciate any help you call can offer. Also - I need this to
be done as a macro. I know how to filter the column manually to get
what I want but I need this as a macro so people who are not familiar
with Excel can simply open the workbook (I have the macro set to run
automatically), enter the time value (ex. 2:30 pm) and they'll get all
the rows in which the time is greater than or equal to 2:30 pm (the
rows with an earlier time having been deleted by the macro).

Thanks in advance.
MP

 

[Guest]

I just wrote a macro for deleting rows, so I modified it to your needs.

Sub DeleteSomeRows(dMaxTime as Double)
' This macro deletes rows containing a value less than dMaxTime in column D

For i = 1 To 65536 ' <-- Adapt to your needs!
If Cells(i, "D") < dMaxTime
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1 ' as we just deleted a row, we should not increase i
End If
Next i

End Sub

Stephane.

 

[pilotom]

On Jul 21, 1:08 pm, Stephane Quenson

If Cells(i, "D") < dMaxTime

Hi Stephanie,

Thank you so much for the help with this. However, I got a syntax
message on this line above. I'm using Excel 2000. Do you think that
might be the problem if you're using a newer version.

MP

 

[Guest]

You said that your user has a place to enter the time. You should take this
value and pass it as a parameter to the routine. Note that the time in Excel
is represented as a fraction of 1, e.g. 0.25 corresponds to 06:00 and 0.75 to
18:00. If the time is stored in cell G6, you can call the routine this way:
DeleteSomeRows(Cells("G6"))