Specific Range Deletion

bodhisatvaofboogie · 05-23-2006, 02:40 PM

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

Jim Thomlinson · 05-23-2006, 02:55 PM

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

bodhisatvaofboogie · 05-23-2006, 03:10 PM

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

Jim Thomlinson · 05-23-2006, 03:25 PM

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

bodhisatvaofboogie · 05-23-2006, 03:35 PM

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

Bob Phillips · 05-23-2006, 03:35 PM

Your code should work. What are you seeing?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bodhisatvaofboogie" <[email protected]> wrote in
message news:[email protected]...
> 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 !!!!

bodhisatvaofboogie · 05-24-2006, 07:45 AM

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" <[email protected]> wrote in
> message news:[email protected]...
> > 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 !!!!
>
>
>

Bob Phillips · 05-24-2006, 08:35 AM

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" <[email protected]> wrote in
message news:[email protected]...
> 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" <[email protected]>
wrote in
> > message news:[email protected]...
> > > 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 !!!!
> >
> >
> >

bodhisatvaofboogie · 05-24-2006, 08:40 AM

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" <[email protected]> wrote in
> > message news:[email protected]...
> > > 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 !!!!
> >
> >
> >

bodhisatvaofboogie · 05-24-2006, 09:35 AM

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" <[email protected]> wrote in
> message news:[email protected]...
> > 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" <[email protected]>
> wrote in
> > > message news:[email protected]...
> > > > 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 !!!!
> > >
> > >
> > >
>
>
>