Delete all rows where Column A contains Text
 

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?

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


In article ,
"D Hafer - TFE" wrote:

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?

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?


"JE McGimpsey" wrote:

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


In article ,
"D Hafer - TFE" wrote:

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?

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




In article ,
"D Hafer - TFE" wrote:

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?


"JE McGimpsey" wrote:

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


In article ,
"D Hafer - TFE" wrote:

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?