There are some values in cells in column "M" that are blank. If the cell is
null or blank I want to delete the entire row. I borrowed this script and I
don't understand what the "Is Nothing" stands for. Does it stand for "null"?
How can I fix it to delete the rows I don't need.

thanks,



-----------code----------------
Sub deleteRows()

Dim Rng As Range
Dim rngToSearch As Range
Dim rngToDelete As Range


With ActiveSheet
Set rngToSearch = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp))
End With

For Each Rng In rngToSearch
If IsNull Then
If rngToDelete Is Nothing Then
Set rngToDelete = Rng
Else
Set rngToDelete = Union(rngToDelete, Rng)
End If
End If
Next Rng

If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

End Sub

I put the if test wrong s/b if IsNull(rng).
I will repost.
sorry

"Janis" wrote:

There are some values in cells in column "M" that are blank. If the cell is
null or blank I want to delete the entire row. I borrowed this script and I
don't understand what the "Is Nothing" stands for. Does it stand for "null"?
How can I fix it to delete the rows I don't need.

thanks,



-----------code----------------
Sub deleteRows()

Dim Rng As Range
Dim rngToSearch As Range
Dim rngToDelete As Range


With ActiveSheet
Set rngToSearch = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp))
End With

For Each Rng In rngToSearch
If IsNull Then
If rngToDelete Is Nothing Then
Set rngToDelete = Rng
Else
Set rngToDelete = Union(rngToDelete, Rng)
End If
End If
Next Rng

If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

End Sub

The whole thing can be done easier than that. Try this...

Sub DeleteBlanks()
On Error Resume Next
Columns("M").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
On Error GoTo 0
End Sub
--
HTH...

Jim Thomlinson


"Janis" wrote:

I put the if test wrong s/b if IsNull(rng).
I will repost.
sorry

"Janis" wrote:

There are some values in cells in column "M" that are blank. If the cell is
null or blank I want to delete the entire row. I borrowed this script and I
don't understand what the "Is Nothing" stands for. Does it stand for "null"?
How can I fix it to delete the rows I don't need.

thanks,



-----------code----------------
Sub deleteRows()

Dim Rng As Range
Dim rngToSearch As Range
Dim rngToDelete As Range


With ActiveSheet
Set rngToSearch = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp))
End With

For Each Rng In rngToSearch
If IsNull Then
If rngToDelete Is Nothing Then
Set rngToDelete = Rng
Else
Set rngToDelete = Union(rngToDelete, Rng)
End If
End If
Next Rng

If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

End Sub

Jim:
For some reason this compiles but it does not delete any of the rows. It
doesn't delete anything. In column "M" I have a necessary fields. If that
field is blank then I need the whole row removed so the report will make
sense. It looks like it should work but it doesn't do anything. Should I
have a loop?
Thanks,


"Jim Thomlinson" wrote:

The whole thing can be done easier than that. Try this...

Sub DeleteBlanks()
On Error Resume Next
Columns("M").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
On Error GoTo 0
End Sub
--
HTH...

Jim Thomlinson


"Janis" wrote:

I put the if test wrong s/b if IsNull(rng).
I will repost.
sorry

"Janis" wrote:

There are some values in cells in column "M" that are blank. If the cell is
null or blank I want to delete the entire row. I borrowed this script and I
don't understand what the "Is Nothing" stands for. Does it stand for "null"?
How can I fix it to delete the rows I don't need.

thanks,



-----------code----------------
Sub deleteRows()

Dim Rng As Range
Dim rngToSearch As Range
Dim rngToDelete As Range


With ActiveSheet
Set rngToSearch = .Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp))
End With

For Each Rng In rngToSearch
If IsNull Then
If rngToDelete Is Nothing Then
Set rngToDelete = Rng
Else
Set rngToDelete = Union(rngToDelete, Rng)
End If
End If
Next Rng

If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

End Sub