I have been going through past posts, and they are very helpful. However,
most of them speak to deleting rows with specific data or numbers. I would
like to write a macro that delets all rows in which column A is empty or has
anything other than a date format i.e. dd/mm/yy. Some cells have the text
"DATE", some have "___", some are empty, etc.

Thanks

Use the IsDate() function, for instance:

dim rng as Range
dim rngAll as Range

set rngAll = range("A1:A54") 'or whatever range

for each rng in rngAll
if IsDate(rng.Value) then
rng.EntireRow.Delete
End If
Next rng

Set rng = Nothing
Set rngAll = Nothing


"Terri Miller" wrote:

I have been going through past posts, and they are very helpful. However,
most of them speak to deleting rows with specific data or numbers. I would
like to write a macro that delets all rows in which column A is empty or has
anything other than a date format i.e. dd/mm/yy. Some cells have the text
"DATE", some have "___", some are empty, etc.

Thanks

Hi Terri

You can use this to delete all cells without a date in A1:A100
More information you can find here
http://www.rondebruin.nl/delete.htm

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf Not IsDate(.Cells(Lrow, "A").Value) Then .Rows(Lrow).Delete

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Terri Miller" wrote in message ...
I have been going through past posts, and they are very helpful. However,
most of them speak to deleting rows with specific data or numbers. I would
like to write a macro that delets all rows in which column A is empty or has
anything other than a date format i.e. dd/mm/yy. Some cells have the text
"DATE", some have "___", some are empty, etc.

Thanks

Eric:

I entered the following however, no rows were deleted. Remember, I want to
keep the rows with dates and delete all others. Thanks


Sub Testme3()


Dim rng As Range
Dim rngAll As Range

Set rngAll = Range("A1:A1000") 'or whatever range

For Each rng In rngAll
If IsDate(rng.Value) Then
rng.EntireRow.Delete
End If
Next rng

Set rng = Nothing
Set rngAll = Nothing

End Sub



"Eric White" wrote:

Use the IsDate() function, for instance:

dim rng as Range
dim rngAll as Range

set rngAll = range("A1:A54") 'or whatever range

for each rng in rngAll
if IsDate(rng.Value) then
rng.EntireRow.Delete
End If
Next rng

Set rng = Nothing
Set rngAll = Nothing


"Terri Miller" wrote:

I have been going through past posts, and they are very helpful. However,
most of them speak to deleting rows with specific data or numbers. I would
like to write a macro that delets all rows in which column A is empty or has
anything other than a date format i.e. dd/mm/yy. Some cells have the text
"DATE", some have "___", some are empty, etc.

Thanks

"Terri Miller" wrote:

I have been going through past posts, and they are very helpful. However,
most of them speak to deleting rows with specific data or numbers. I would
like to write a macro that delets all rows in which column A is empty or has
anything other than a date format i.e. dd/mm/yy. Some cells have the text
"DATE", some have "___", some are empty, etc.

Thanks

Terri

I'd copy the data to a new sheet before running this.

Sub test()
Dim r, nr, col, ncol, c
Application.ScreenUpdating = False
Selection.SpecialCells(xlCellTypeLastCell).Select
nr = ActiveCell.Row
'test format of each cell

For r = 1 To nr
Cells(r, 1).Select
If ActiveCell.NumberFormat < "dd/mm/yy" _
Or IsEmpty(ActiveCell) _
Or Not Application.IsNumber(ActiveCell) Then
ActiveCell.EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End Sub

Peter Atherton

Perfect.

I will take the time to understand what you wrote tomorrow. Thanks for the
help.

"Ron de Bruin" wrote:

Hi Terri

You can use this to delete all cells without a date in A1:A100
More information you can find here
http://www.rondebruin.nl/delete.htm

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf Not IsDate(.Cells(Lrow, "A").Value) Then .Rows(Lrow).Delete

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Terri Miller" wrote in message ...
I have been going through past posts, and they are very helpful. However,
most of them speak to deleting rows with specific data or numbers. I would
like to write a macro that delets all rows in which column A is empty or has
anything other than a date format i.e. dd/mm/yy. Some cells have the text
"DATE", some have "___", some are empty, etc.

Thanks

Oops!

Ron's reply is the perfect answer Mine was rubbish.

Peter

"PeterAtherton" wrote:



"Terri Miller" wrote:

I have been going through past posts, and they are very helpful. However,
most of them speak to deleting rows with specific data or numbers. I would
like to write a macro that delets all rows in which column A is empty or has
anything other than a date format i.e. dd/mm/yy. Some cells have the text
"DATE", some have "___", some are empty, etc.

Thanks

Terri

I'd copy the data to a new sheet before running this.

Sub test()
Dim r, nr, col, ncol, c
Application.ScreenUpdating = False
Selection.SpecialCells(xlCellTypeLastCell).Select
nr = ActiveCell.Row
'test format of each cell

For r = 1 To nr
Cells(r, 1).Select
If ActiveCell.NumberFormat < "dd/mm/yy" _
Or IsEmpty(ActiveCell) _
Or Not Application.IsNumber(ActiveCell) Then
ActiveCell.EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End Sub

Peter Atherton

Further to this...how can I delete rows with any text? Or maybe, just select
the cells with numbers, copy them and move them to another worksheet?
Currently the entire spreadsheet is formatted as general.

Thanks,
Mrs. Robinson

"Ron de Bruin" wrote:

Hi Terri

You can use this to delete all cells without a date in A1:A100
More information you can find here
http://www.rondebruin.nl/delete.htm

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf Not IsDate(.Cells(Lrow, "A").Value) Then .Rows(Lrow).Delete

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Terri Miller" wrote in message ...
I have been going through past posts, and they are very helpful. However,
most of them speak to deleting rows with specific data or numbers. I would
like to write a macro that delets all rows in which column A is empty or has
anything other than a date format i.e. dd/mm/yy. Some cells have the text
"DATE", some have "___", some are empty, etc.

Thanks

Further to this...how can I delete rows with any text?

You probably will not get an explicit response to the question. I am
neither a professional programmer nor an expert programmer, but in VBA,
numbers can be text and empty cells can be read as numeric values. So, in
my tiny world, there is no simple way to approach the question. Perhaps if
you would re-post and describe what your end objective is and what the data
layout is that you have to work with, someone could offer some kind of
solution for you.


"Mrs. Robinson" wrote in message
...
Further to this...how can I delete rows with any text? Or maybe, just
select
the cells with numbers, copy them and move them to another worksheet?
Currently the entire spreadsheet is formatted as general.

Thanks,
Mrs. Robinson

"Ron de Bruin" wrote:

Hi Terri

You can use this to delete all cells without a date in A1:A100
More information you can find here
http://www.rondebruin.nl/delete.htm

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in
the cell

ElseIf Not IsDate(.Cells(Lrow, "A").Value) Then
.Rows(Lrow).Delete

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Terri Miller" wrote in message
...
I have been going through past posts, and they are very helpful.
However,
most of them speak to deleting rows with specific data or numbers. I
would
like to write a macro that delets all rows in which column A is empty
or has
anything other than a date format i.e. dd/mm/yy. Some cells have the
text
"DATE", some have "___", some are empty, etc.

Thanks

I'll do that...thanks!

"JLGWhiz" wrote:

Further to this...how can I delete rows with any text?

You probably will not get an explicit response to the question. I am
neither a professional programmer nor an expert programmer, but in VBA,
numbers can be text and empty cells can be read as numeric values. So, in
my tiny world, there is no simple way to approach the question. Perhaps if
you would re-post and describe what your end objective is and what the data
layout is that you have to work with, someone could offer some kind of
solution for you.


"Mrs. Robinson" wrote in message
...
Further to this...how can I delete rows with any text? Or maybe, just
select
the cells with numbers, copy them and move them to another worksheet?
Currently the entire spreadsheet is formatted as general.

Thanks,
Mrs. Robinson

"Ron de Bruin" wrote:

Hi Terri

You can use this to delete all cells without a date in A1:A100
More information you can find here
http://www.rondebruin.nl/delete.htm

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in
the cell

ElseIf Not IsDate(.Cells(Lrow, "A").Value) Then
.Rows(Lrow).Delete

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Terri Miller" wrote in message
...
I have been going through past posts, and they are very helpful.
However,
most of them speak to deleting rows with specific data or numbers. I
would
like to write a macro that delets all rows in which column A is empty
or has
anything other than a date format i.e. dd/mm/yy. Some cells have the
text
"DATE", some have "___", some are empty, etc.

Thanks