dcsimg
Results 1 to 8 of 8

Thread: [RESOLVED] Getting rid up unused rows at bottom

  1. #1

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,025

    Resolved [RESOLVED] Getting rid up unused rows at bottom

    I'm working with a large Excel workbook that has many columns formatted with fill colors. The formatting extends down 13,000+ rows when only 2000 or so are actually needed, which makes the workbook larger than necessary. How do I get rid of the unneeded 11,000 rows.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,721

    Re: Getting rid up unused rows at bottom

    Code:
    Set r = Cells(1, 1).End(xlUp).Offset(1)
    Set r = r.Resize(Rows.Count - r.Rows.Count)
    With r
        .EntireRow.Interior.ColorIndex = -4142
    End With
    you can change any other formatting etc within the with block
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,025

    Re: Getting rid up unused rows at bottom

    That's a nice technique but after adjusting the code to account for the fact that I don't want to touch the first 5000 rows like this
    Code:
    Sub Reformat()
    Dim r As Range
    Set r = Cells(1, 1).End(xlUp).Offset(5000)
    Set r = r.Resize(Rows.Count - 5000)
    With r
        .EntireRow.Interior.ColorIndex = -4142
        .EntireRow.ClearFormats
    End With
    End Sub
    And while Ctrl+End now points to row 5000 whereas before it was 13,000 something, the file is larger than before.

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,532

    Re: Getting rid up unused rows at bottom

    Assuming you have data in column A for all "filled rows:"

    Code:
    Sub removeFormat()
        Dim ws As Worksheet
        Dim lr As Long
        
        Set ws = ActiveSheet
        With ws
            lr = .Range("a1").End(xlDown).Row
            .Range("a" & lr + 1 & ":a" & Rows.Count).Delete
        End With
    End Sub

  5. #5

  6. #6

  7. #7

  8. #8
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,176

    Re: [RESOLVED] Getting rid up unused rows at bottom

    Another option is to filter the sheet to show only the rows you want to keep, then copy and paste into a new workbook.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width