+ Reply to Thread
Results 1 to 10 of 10

Specific Range Deletion

  1. #1
    bodhisatvaofboogie
    Guest

    Specific Range Deletion

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

  2. #2
    Jim Thomlinson
    Guest

    RE: Specific Range Deletion

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


  3. #3
    bodhisatvaofboogie
    Guest

    RE: Specific Range Deletion

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


  4. #4
    Jim Thomlinson
    Guest

    RE: Specific Range Deletion

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


  5. #5
    bodhisatvaofboogie
    Guest

    RE: Specific Range Deletion

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


  6. #6
    Bob Phillips
    Guest

    Re: Specific Range Deletion

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




  7. #7
    bodhisatvaofboogie
    Guest

    Re: Specific Range Deletion

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

    >
    >
    >


  8. #8
    Bob Phillips
    Guest

    Re: Specific Range Deletion

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

    > >
    > >
    > >




  9. #9
    bodhisatvaofboogie
    Guest

    Re: Specific Range Deletion

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

    > >
    > >
    > >


  10. #10
    bodhisatvaofboogie
    Guest

    Re: Specific Range Deletion

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

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1