Delete Row if text or empty

T

Terri Miller

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
 
E

Eric White

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
 
R

Ron de Bruin

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
 
T

Terri Miller

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
 
P

PeterAtherton

Terri Miller said:
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
Click to expand...

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
 
T

Terri Miller

Perfect.

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

Mrs. Robinson

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
 
J

JLGWhiz

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.
 
M

Mrs. Robinson

I'll do that...thanks!

JLGWhiz said:
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.
Click to expand...
 
Top