while deleting rows it finds an error - error trapping

[Janis]

Okay, my code seems to be working but it stops after deleting the first two
rows.
It must find an error then return to 0 but what is the error? The only
thing I see is there are two cells together in column M that are blank.
There are a whold bunch more. HOw do I trap for the error so this script
will finish.
thanks, sorry for the multiple posts.
-----------code------
Sub DeleteRowsBlankMColumns()
With ActiveSheet
On Error Resume Next
.Columns("M").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End With
End Sub

 

[Jim Thomlinson]

From your previous post give this a try. It is probalby not an error but
rather blank space characters or the blanks are the result of a formula...

How about this. It should delete all of the blank whether they are the result
of a formula or if the cell contains blank spaces... It is similar to what
you first posted...

Sub DeleteBlanks()
Dim rngToSearch As Range
Dim rng As Range
Dim rngToDelete As Range

With ActiveSheet
On Error Resume Next
Columns("M").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

Set rngToSearch = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp))
For Each rng In rngToSearch
If Trim(rng.Value) = "" Then
If rngToDelete Is Nothing Then
Set rngToDelete = rng
Else
Set rngToDelete = Union(rng, rngToDelete)
End If
Next rng
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End With
End Sub

 

[Janis]

That is a very nice script. I am sure there is dirty data in it. It comes
from a SQL server or is downloaded as a report. This company uses excel for
everything instead of databases. some files they copy several times. tnx,