Reset Range after row delete

[Guest]

Is there a macro command to rest the worksheet region/range after deleting
several rows.

After I delete and then go to last cell or current region, it still shows
the area before the delete.

Thanks for your help.

 

[Don Guillett]

If you "cleared" instead of deleting, delete and then SAVE. Try it.

 

[Guest]

I used the following to delete row.

Sub delete_sme_rows()

Dim r As Range, j As Long

Set r = ActiveSheet.UsedRange
j = r.Rows.Count + r.Row
Set rdel = Cells(j, "A")
For i = 1 To j - 1
If Cells(i, "C").Value = "SME" Then
Set rdel = Union(rdel, Cells(i, "A"))
End If
Next

rdel.EntireRow.Delete

End Sub


Can I reset without saving so the next macro in line will know the correct
region?

 

[Dana DeLouis]

Can I reset without saving so the next macro in line will know

the correct region?

Hi. Use
ActiveSheet.UsedRange

at the end of your code.

It's still buggy in 2003, but should work for what you are doing.

 

[Guest]

Just an after thought type comment about your code. You might want to
consider changing the + r.row to just + 1. You have r set to UsedRange which
could be many rows, however, it seems that r.row only gives you a value of 1
anyhow. It would not change the results, but it would make more sense.

 

[Tom Ogilvy]

You appear to have the mistaken impression that the usedrange always starts
with first row. Just to demonstrate from the immediate window:

set r = Activesheet.UsedRange
? r.row
22

so your suggestion is based on a false premise. While most of the time it
would be true, the whole point of using r.row is to account for the fact
that it may not.

 

[Guest]

Tom, My comments were based on the fact that r was set to used range and used
range will consider all cells in the workbook that contain data. When
testing his macro I noticed that the value of r.row varied with different
iterations until I cleared the entire worksheet and started fresh. It just
seemed to me that r.row was sort of a loose way to get the number he needs.

 

[Tom Ogilvy]

seemed to me that r.row was sort of a loose way to get the number he

needs.

As the OP showed it, to get the last row of the used range, it is a widely
used method and certainly nothing wrong with it.

 

[Tom Ogilvy]

Just to be precise:

to get the last row of the used range

should have said

to get the row after the last row of the used range

An additional 1 is subtracted to get the last row of the used range.

 

[Guest]

Well, this old feller has learned somethin.

Just to be precise:

should have said

to get the row after the last row of the used range

An additional 1 is subtracted to get the last row of the used range.