What is a good formula for Deleting a specific range of rows? I am trying to
figure out a way to delete all but the last 10 rows from a large set. I'm
implementing it into a macro, and am struggling with how to get it done. I
currently am experimenting with this:

Dim r As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "V").End(xlUp).Row
Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))
r.EntireRow.Delete

Though it's not working. Any better ideas? THANKS !!!!

Give this a try...

Range("V2", Cells(Rows.Count, "V").End(xlUp).Offset(-10, 0)).Entirerow.Delete

Keep in mind that this will crash if there are less than 10 rows of data...
--
HTH...

Jim Thomlinson


"bodhisatvaofboogie" wrote:

What is a good formula for Deleting a specific range of rows? I am trying to
figure out a way to delete all but the last 10 rows from a large set. I'm
implementing it into a macro, and am struggling with how to get it done. I
currently am experimenting with this:

Dim r As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "V").End(xlUp).Row
Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))
r.EntireRow.Delete

Though it's not working. Any better ideas? THANKS !!!!

that by itself didn't work. Make sure you break it down simply for me, I'm
still new to the code stuff :) So perhaps I didn't plug it in correctly.

"Jim Thomlinson" wrote:

Give this a try...

Range("V2", Cells(Rows.Count, "V").End(xlUp).Offset(-10, 0)).Entirerow.Delete

Keep in mind that this will crash if there are less than 10 rows of data...
--
HTH...

Jim Thomlinson


"bodhisatvaofboogie" wrote:

What is a good formula for Deleting a specific range of rows? I am trying to
figure out a way to delete all but the last 10 rows from a large set. I'm
implementing it into a macro, and am struggling with how to get it done. I
currently am experimenting with this:

Dim r As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "V").End(xlUp).Row
Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))
r.EntireRow.Delete

Though it's not working. Any better ideas? THANKS !!!!

This code will work on the active sheet. Put it inot a standard code module
and then run the code. It will delete all but the last 10 rows (leaving row 1
as I assumed it to be a header row). It looks at column V...

Sub Test()

Range("V2", Cells(Rows.Count, "V").End(xlUp).Offset(-10,
0)).EntireRow.Delete
End Sub
--
HTH...

Jim Thomlinson


"bodhisatvaofboogie" wrote:

that by itself didn't work. Make sure you break it down simply for me, I'm
still new to the code stuff :) So perhaps I didn't plug it in correctly.

"Jim Thomlinson" wrote:

Give this a try...

Range("V2", Cells(Rows.Count, "V").End(xlUp).Offset(-10, 0)).Entirerow.Delete

Keep in mind that this will crash if there are less than 10 rows of data...
--
HTH...

Jim Thomlinson


"bodhisatvaofboogie" wrote:

What is a good formula for Deleting a specific range of rows? I am trying to
figure out a way to delete all but the last 10 rows from a large set. I'm
implementing it into a macro, and am struggling with how to get it done. I
currently am experimenting with this:

Dim r As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "V").End(xlUp).Row
Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))
r.EntireRow.Delete

Though it's not working. Any better ideas? THANKS !!!!

I'm not sure then what I am doing wrong. It just simply won't work on it's
own like that. I'll keep trying to figure it out.

"Jim Thomlinson" wrote:

This code will work on the active sheet. Put it inot a standard code module
and then run the code. It will delete all but the last 10 rows (leaving row 1
as I assumed it to be a header row). It looks at column V...

Sub Test()

Range("V2", Cells(Rows.Count, "V").End(xlUp).Offset(-10,
0)).EntireRow.Delete
End Sub
--
HTH...

Jim Thomlinson


"bodhisatvaofboogie" wrote:

that by itself didn't work. Make sure you break it down simply for me, I'm
still new to the code stuff :) So perhaps I didn't plug it in correctly.

"Jim Thomlinson" wrote:

Give this a try...

Range("V2", Cells(Rows.Count, "V").End(xlUp).Offset(-10, 0)).Entirerow.Delete

Keep in mind that this will crash if there are less than 10 rows of data...
--
HTH...

Jim Thomlinson


"bodhisatvaofboogie" wrote:

What is a good formula for Deleting a specific range of rows? I am trying to
figure out a way to delete all but the last 10 rows from a large set. I'm
implementing it into a macro, and am struggling with how to get it done. I
currently am experimenting with this:

Dim r As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "V").End(xlUp).Row
Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))
r.EntireRow.Delete

Though it's not working. Any better ideas? THANKS !!!!

Your code should work. What are you seeing?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bodhisatvaofboogie" wrote in
message ...
What is a good formula for Deleting a specific range of rows? I am trying
to
figure out a way to delete all but the last 10 rows from a large set.
I'm
implementing it into a macro, and am struggling with how to get it done.
I
currently am experimenting with this:

Dim r As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "V").End(xlUp).Row
Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))
r.EntireRow.Delete

Though it's not working. Any better ideas? THANKS !!!!

It goes into Debug and highlights this line:

Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))

So I'm not sure what is wrong with it, but it doesn't work like I want it to
:)


"Bob Phillips" wrote:

Your code should work. What are you seeing?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bodhisatvaofboogie" wrote in
message ...
What is a good formula for Deleting a specific range of rows? I am trying
to
figure out a way to delete all but the last 10 rows from a large set.
I'm
implementing it into a macro, and am struggling with how to get it done.
I
currently am experimenting with this:

Dim r As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "V").End(xlUp).Row
Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))
r.EntireRow.Delete

Though it's not working. Any better ideas? THANKS !!!!

I'll bet that it is caused because the lastrow is not greater than 10, so
the range is invalid.

What should happen if lastrow is say 10, which rows should it delete? Ditto
lastrow = 6?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bodhisatvaofboogie" wrote in
message ...
It goes into Debug and highlights this line:

Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))

So I'm not sure what is wrong with it, but it doesn't work like I want it
to
:)


"Bob Phillips" wrote:

Your code should work. What are you seeing?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bodhisatvaofboogie"
wrote in
message ...
What is a good formula for Deleting a specific range of rows? I am
trying
to
figure out a way to delete all but the last 10 rows from a large set.
I'm
implementing it into a macro, and am struggling with how to get it
done.
I
currently am experimenting with this:

Dim r As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "V").End(xlUp).Row
Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))
r.EntireRow.Delete

Though it's not working. Any better ideas? THANKS !!!!

I got it figured out with a similar, but not the same code. Thanks, all your
input has been helpful. :)

"bodhisatvaofboogie" wrote:

It goes into Debug and highlights this line:

Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))

So I'm not sure what is wrong with it, but it doesn't work like I want it to
:)


"Bob Phillips" wrote:

Your code should work. What are you seeing?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bodhisatvaofboogie" wrote in
message ...
What is a good formula for Deleting a specific range of rows? I am trying
to
figure out a way to delete all but the last 10 rows from a large set.
I'm
implementing it into a macro, and am struggling with how to get it done.
I
currently am experimenting with this:

Dim r As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "V").End(xlUp).Row
Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))
r.EntireRow.Delete

Though it's not working. Any better ideas? THANKS !!!!

Dim r As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "S").End(xlUp).Row
If lastrow < 11 Then Exit Sub
Set r = Range(Cells(2, "S"), Cells(lastrow - 10, "S"))
r.EntireRow.Delete

This seemed to work for me. It goes to the bottom of the cells in a row,
moves up 10, then selects and deletes everything above that up to row 2,
leaving the header row in tact. This has worked for me thus far with no
errors.

"Bob Phillips" wrote:

I'll bet that it is caused because the lastrow is not greater than 10, so
the range is invalid.

What should happen if lastrow is say 10, which rows should it delete? Ditto
lastrow = 6?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bodhisatvaofboogie" wrote in
message ...
It goes into Debug and highlights this line:

Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))

So I'm not sure what is wrong with it, but it doesn't work like I want it
to
:)


"Bob Phillips" wrote:

Your code should work. What are you seeing?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bodhisatvaofboogie"
wrote in
message ...
What is a good formula for Deleting a specific range of rows? I am
trying
to
figure out a way to delete all but the last 10 rows from a large set.
I'm
implementing it into a macro, and am struggling with how to get it
done.
I
currently am experimenting with this:

Dim r As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "V").End(xlUp).Row
Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V"))
r.EntireRow.Delete

Though it's not working. Any better ideas? THANKS !!!!