Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA - delete last 7 cells (2000 sr1)

    What I'm looking for is probably a simple loop, but I don't know enough VBA to get it to work on my own.

    I have a spreadsheet with 40224 rows. Each row has between 30 and 40 cells of information, then blank fields. The last 7 cells in each row are useless, and I want to clear the data in those fields. Please help! Thanks!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA - delete last 7 cells (2000 sr1)

    Are these seven cells always the same columns in each row, or do you just want to clear the last seven cells in every row? Is there a column that is guranteed to have something in it in every row?
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Aug 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA - delete last 7 cells (2000 sr1)

    Just the last seven cells in every row (they'll be different).
    Every column is guaranteed to have data through the last cell, except for the fact that the number of columns in each row is different (between 30 and 40 cells to a row).

    Hope this clarifies...

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA - delete last 7 cells (2000 sr1)

    See if this will do what you want.

    <pre>Public Sub DelLast7()
    Dim I As Long, lLastCol As Long
    I = 0
    With Worksheets("Sheet1").Range("A1")
    While .Offset(I, 0).Value <> ""
    lLastCol = .Offset(I, 255).End(xlToLeft).Column - 1
    Worksheets("Sheet1").Range(.Offset(I, lLastCol - 6), .Offset(I, lLastCol)).Clear
    I = I + 1
    Wend
    End With
    End Sub
    </pre>

    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Aug 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA - delete last 7 cells (2000 sr1)

    I actually decided to go back to my .txt feed and get rid of the junk there rather than programming in VB... I'll have to take a course eventually but until then...

    Thank you for your help! Have a terrific New Year!

Posting Permissions

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