delete rows with null values in "M" column
"Janis" wrote in message
...
I'm trying to delete the rows where the cell "M" has a null or blank
value.
I borrowed this script and it compiles. The question I have is what is
the
test
if range to delete
Is NOthing
then....
mean. Does that stand for null??
No, it is checkong to see whether any items to delete have already been
identified, so as to determine whether to prime the ranbge, or add to it
(Union)
Also what is the union of the rng to delete?
Do I just do
if IsNull(rng)
set rng to delete = rng
See above.
The code doesn't work for me. this does
Dim rngToSearch As Range
Dim rngToDelete As Range
Dim rng As Range
With ActiveSheet
Set rngToSearch = .Range(.Range("M1"), .Cells(Rows.Count,
"M").End(xlUp))
End With
For Each rng In rngToSearch
If IsEmpty(rng) 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
But you can do it simpler
Dim rngToDelete As Range
With ActiveSheet
On Error Resume Next
Set rngToDelete = .Range(.Range("M1"), .Cells(Rows.Count,
"M").End(xlUp)).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
End With
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
|