PDA

View Full Version : Solved: Date Delete



oleg_v
04-11-2010, 06:19 AM
hi

i need some help with macro code

how can i delete every row that contains a date meaneng if the cell in sheet1 contains a date need to delete the entaire row

thanks

GTO
04-11-2010, 06:45 AM
Hi Oleg,

It is hard to answer when we are asked to presume so much. We end up wasting time trying to help, only to find out that we assumed too much. Could you specify:

Are we looking for dates in one particular column?
If yes, which column?
Are we starting at row 1, or is/are there header row(s)?

Thanks,

Mark

oleg_v
04-11-2010, 06:56 AM
i am very sorry

i need to start at row 1 and look every cell and if the cell contains a date delete the row

i will try to explain better
usualy i paste an example but i do not have it right know sorry

thanks

GTO
04-11-2010, 07:05 AM
No problem Oleg, I am only trying to help us in helping you better :-)

Could you answer my other questions?

Are we looking in only one column, and which column might that be?

Mark

oleg_v
04-11-2010, 07:13 AM
i put it this way:

we are talking about only one column in this column the same date can repeate one time or more.
the column is changing every time

thanks for understanding

oleg_v
04-11-2010, 07:15 AM
i wanted to explain "changing every time" i meant every time i run the macro


thanks

GTO
04-11-2010, 07:39 AM
i put it this way:

we are talking about only one column in this column the same date can repeate one time or more.
the column is changing every time

thanks for understanding

Wow! I am glad I asked the 'extra' questions Oleg. See, I would have assumed from your first post, that we were running up a column, and if there was 'a date', meaning ANY date, that we'd be deleting the row.

I must get to bed for a bit, but maybe this will help a tiny bit. If I restated the problem/issue this way, would it be accurate?


I would like to look for a certain date in all rows of a particular column. If this certain date is found, then we need to delete the entire row.
I have a complication, in that, it may be a different column from time to time. Thus - I need to offer the user two choices:
The user needs to be able to type in a date, or pick a date.
The user needs to be able to specify what column to look in.Does that sound like an accurate portrayal?

Mark

Bob Phillips
04-11-2010, 09:16 AM
Public Sub ProcessData()
Dim i As Long
Dim NumRows As Long
Dim FirstRow As Long
Dim rngRow As Range
Dim cell As Range

With ActiveSheet.UsedRange

NumRows = .Rows.Count
FirstRow = .Cells(1, 1).Row
For i = NumRows To 1 Step -1

For Each cell In .Rows(i).Cells

If IsDate(cell.Value) Then

cell.EntireRow.Delete
Exit For
End If
Next cell
Next i
End With

End Sub