Reset Range after row delete
 

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.

Reset Range after row delete
 

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

--
Don Guillett
SalesAid Software

"DeanT" wrote in message
...
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.

Reset Range after row delete
 

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?

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"DeanT" wrote in message
...
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.

Reset Range after row delete
 

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.
--
Dana DeLouis
Windows XP & Office 2003


"DeanT" wrote in message
...
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?

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"DeanT" wrote in message
...
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.

Reset Range after row delete
 

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.

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"DeanT" wrote in message
...
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.

Reset Range after row delete
 

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.

--
Regards,
Tom Ogilvy



"JLGWhiz" wrote in message
...
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.

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"DeanT" wrote in message
...
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.

Reset Range after row delete
 

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" wrote:

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.

--
Regards,
Tom Ogilvy



"JLGWhiz" wrote in message
...
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.

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"DeanT" wrote in message
...
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.

Reset Range after row delete
 

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.

--
Regards,
Tom Ogilvy


"JLGWhiz" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy



"JLGWhiz" wrote in message
...
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.

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"DeanT" wrote in message
...
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.

Reset Range after row delete
 

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.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"JLGWhiz" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy



"JLGWhiz" wrote in message
...
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.

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"DeanT" wrote in message
...
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.

Reset Range after row delete
 

Well, this old feller has learned somethin.

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"JLGWhiz" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy



"JLGWhiz" wrote in message
...
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.

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"DeanT" wrote in message
...
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.