Date ranges in macros

[Zak]

Hi, i asked this question in a previous thread but it still hasnt been
answered and a couple of my other questions have so was wondering whether i
need to re-submit.

i want to write a macro that will recognise that the date in column x is
beyond
todays date and then delete the rows.

So, if the date in column X is beyond/greater than todays date (and this date
should be the current day) then the rows should be deleted.

thanks.

 

[Lazzzx]

Hi Zak,

This routine should do your job. The dates to test are in column x:

Sub DeleteRowsbyDate()
For Each cll In Range([x1], [x1].End(xlDown))
If IsDate(cll) And cll > Now() Then _
cll.EntireRow.Delete
Next
End Sub

CAUTION!! Use with care... you cannot undo these changes.

regards,
Lazzzx

 

[FSt1]

hi
I think your have left out a lot for someone to give a good reply but i will
make some guesses.
assume that you are in a loop, with the key being column x.....
If activecell.Value > Date Then
activecell.EntireRow.Delete shift:=xlUp
End If

Personally i don't like using activecell in a loop. it requires select which
slows things down. i prefer using variables. something like this....
Sub test1()
Dim r As Range
Dim rd As Range
Set r = Range("X2") 'assume a header
Do While Not IsEmpty(r)
Set rd = r.Offset(1, 0)
If r.Value > Date Then
r.EntireRow.Delete shift:=xlUp
End If
Set r = rd 'move down one or next
Loop
End Sub

I'm using a do loop here because i'm dealing with an unknow ie(how may rows)
most programmers perfer the for next loop but each has it's purpose. that's
why vb has both. do loops can get out of control if not coded right ie run
away code. for next loop only do thing a certain number of times then quits
but you must find the limits to restrict it to.
for next loop
Sub test2()
Dim r As Range

lr = Cells(Rows.Count, "X").End(xlUp).Row 'finds the last row in data
Set r = Range("x2:x" & lr)

For Each cell In r
If cell.Value > Date Then
cell.EntireRow.Delete
End If
Next cell

End Sub

hope this wild guessing is giving you ideas
regards
FSt1

 

[Zak]

Thanks for all your guesses. Each one worked but the problem is when i ran
the macro i saw that it only deleted a few lines at a time. I had to run the
macro 3 times (clicked it 3 times) to get it to delete all the rows i wanted!

why is this happening? cant it delete everything with one click?

thanks.