Solved: i am puzzled with it, please give some tips

[martian]

Sub DeleteRow()
Dim sh As Worksheet
Dim rg As Range
For Each sh In ThisWorkbook.Worksheets
Set rg = sh.Cells(65536, "C").End(xlUp)
Do While rg.Row >= 2
If rg.Value = 0 Then
Set rg = rg.Offset(-1, 0)
rg.Offset(1, 0).EntireRow.Delete
Else
Set rg = rg.Offset(-1, 0)
End If
Loop
Next
Set sh=Nothing
Set rg=Nothing
End Sub

This program runs well.
But when i changed this parts:

If rg.Value = 0 Then
Set rg = rg.Offset(-1, 0)
rg.Offset(1, 0).EntireRow.Delete

To:

If rg.Value=0 Then
rg.EntireRow.Delete
Set rg = rg.Offset(-1, 0)

There was an error occurred:runtime error '424';no object
and maybe it's the reason that i deleted the variant 'rg' (rg.EntireRow.Delete )

but i am puzzled with this:
if rg gets the value 'cells(3,3)' and after i run "rg.entirerow.delete"
the range cells(3,3) is still there.

[GTO]

Greetings martian,

Indeed the code:

rg.EntireRow.Delete

starts the problem, as you have basically killed your defined range; thus -

Set rg = rg.Offset(-1, 0)

...fails, as you are trying to Set a range based on a non-existent object.

'//If I understand your question however, this does not mean that...//

Cells(3,3)

'//no longer exists, its just now what was //

Cells(4,3)

'// IF we just deleted an entire row.//

Think of it this way: when you delete a row, everything just moves up a row, and a "new" blank row is "created". Else - after you deleted a row, you would be able to scroll all the way down and row 65536 would no longer exist...

Hope that helps,

Mark

[martian]

Thanks for your response, GTO
It's very helpful for me, i test it with a formula just now.
when i deleted the referenced cell in my formula.
The result just got the value:#REF!.

Thanks anyway

[GTO]

You are of course welcome. It sounds as if your question wasn't just theoretical, but that your code is still failing you somewhere?

If this is the case, post an example workbook ("sanitized" of any private/company/proprietary info), or at least what 'DeleteRow()' is supposed to do, and what's goobering up.

Sorry, I thought it was working, as you relayed , "This program runs well...".

Hope to help,

Mark

[martian]

My question is just theoretical. Because I think that the cells(3,3) is always valid even though you delete the row(3).

and now, i have known the fact that the formulas or programs included cells(3,3) will not work if i deleted it. but i do not well understand it, only remember it as a fact.

[GTO]

Okay, well I thought to check. If all answered, please mark your thread "Resolved". You can find the button under under the Thread Tools at the top of your initial post.

Have a good night, :sleep:

Mark