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??

Also what is the union of the rng to delete?
Do I just do
if IsNull(rng)
set rng to delete = rng
I realize they are trying to be careful but I don't quite understand what is
going on.

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(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

"Janis" <(E-Mail Removed)> wrote in message
news:8A52C1FB-912F-4B53-94FD-(E-Mail Removed)...
> 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

janis,

the code you have is a complicated way of doing what you want but basically
it searches your rand for Null (empty) cells and buils then into a new range
called
rngtodelete

At the end it deletes the lot in one hit

Much simpler

Sub deleteit()
Set myRange = Range("M1", Range("M65536").End(xlUp))
myRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Mike
"Janis" wrote:

> 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??
>
> Also what is the union of the rng to delete?
> Do I just do
> if IsNull(rng)
> set rng to delete = rng
> I realize they are trying to be careful but I don't quite understand what is
> going on.
>
> 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(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
>
>
>

The first time a row-to-be-deleted is encountered, nothing has yet been
assigned to the variable rngToDelete, thus is is equal to "nothing" and it is
safe to assign the current address to the variable. When subsequent
rows-to-be-deleted are encountered, you want to aggregate the new row(s) with
the previously identified row(s), thus the purpose of the UNION() clause is
to assign the new row(s) without losing what was already assigned to the
variable.

"Janis" wrote:

> 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??
>
> Also what is the union of the rng to delete?
> Do I just do
> if IsNull(rng)
> set rng to delete = rng
> I realize they are trying to be careful but I don't quite understand what is
> going on.
>
> 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(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
>
>
>

By the way, I tried this one and it only deleted the first 3 columns with
blank cells in M column and then stopped.

"Bob Phillips" wrote:

>
> "Janis" <(E-Mail Removed)> wrote in message
> news:8A52C1FB-912F-4B53-94FD-(E-Mail Removed)...
> > 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
>
>
>

But only if it has been declared!

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Duke Carey" <(E-Mail Removed)> wrote in message
news:A7B98F49-4726-488F-B6A3-(E-Mail Removed)...
> The first time a row-to-be-deleted is encountered, nothing has yet been
> assigned to the variable rngToDelete, thus is is equal to "nothing" and it
> is
> safe to assign the current address to the variable. When subsequent
> rows-to-be-deleted are encountered, you want to aggregate the new row(s)
> with
> the previously identified row(s), thus the purpose of the UNION() clause
> is
> to assign the new row(s) without losing what was already assigned to the
> variable.
>
> "Janis" wrote:
>
>> 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??
>>
>> Also what is the union of the rng to delete?
>> Do I just do
>> if IsNull(rng)
>> set rng to delete = rng
>> I realize they are trying to be careful but I don't quite understand what
>> is
>> going on.
>>
>> 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(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
>>
>>
>>