Delete all rows where Column A contains Text

D

D Hafer - TFE

I need to create a macro to delete all rows where column A has text. Can
anyone tell me any easy way to do this?
 
J

JE McGimpsey

One way:

Public Sub DeleteRowsWithTextInColumnA()
On Error Resume Next 'in case no text
Columns(1).SpecialCells( _
xlCellTypeConstants, xlTextValues).EntireRow.Delete
On Error GoTo 0
End Sub
 
D

D Hafer - TFE

Actually, I'd like to remove the rows where column A is blank also, let's say
for just the first 300 rows in a sheet. How would this code change?
 
J

JE McGimpsey

One way:

Public Sub DeleteRowsWithTextOrBlanksInA1ToA300()
Dim rDelete
On Error Resume Next 'in case no text or blanks
With Range("A1:A300")
Set rDelete = .SpecialCells( _
xlCellTypeConstants, xlTextValues)
If rDelete Is Nothing Then
Set rDelete = .SpecialCells(xlCellTypeBlanks)
Else
Set rDelete = Union(rDelete, _
.SpecialCells(xlCellTypeBlanks))
End If
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End With
On Error GoTo 0
End Sub
 
Top