How to search for date value in column then delete rows below

[JGKWORK]

Hi and Merry belated Christmas!

Just starting to learn VBA (complete begginer). My first task is to (and could someone show me the VBA for this):

Select Column A (which is a date column) find the first date in this column which equals 00/01/00 then delete this row and all rows below it.

Please help! Many thanks..

 

[Golom]

What does "... below it ..." mean exactly? In SQL databases there is no implicit ordering of rows and you get a specific ordering only if you specify an ORDER BY clause.

If the ordering you are talking about is an ascending ordering by date then you can use an SQL statement like

DELETE * 
From tbl
Where DateField >= #00/01/00#

for Microsoft Access. If you have an ANSI SQL compliant system like SQL Server or Oracle then

DELETE * 
From tbl
Where DateField >= '00/01/00'

I assume that you are aware that "00/01/00" is an invalid date (there's no day or month "00").

 

[JGKWORK]

My apologies, I thought I had written that this is VBA for Excel.

It doesn't matter about the "everything below" I just need to know how to find a date value in a colum.

Thanks...

 

[byundt]

Assuming that the date is an Excel serial number (rather than a text representation of a date), you can find it using the Find method:

Sub DeleteBelowDate()
Dim rg As Range, cel As Range
Dim dat As Date
dat = DateValue("01/11/04")     'Change the date to suit circumstances
Set rg = Range(Cells(1, 1), Cells(65536, 1).End(xlUp))
Set cel = rg.Find(dat, LookIn:=xlFormulas)
If Not cel Is Nothing Then Range(Cells(cel.Row, 1), Cells(65536, 1)).EntireRow.Delete
End Sub

Brad