ExcelBanter - View Single Post

Mrs. Robinson[_2_]

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