Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Deleting extra cells below data

  1. #1
    Board Regular
    Join Date
    Apr 2012
    Location
    GA
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Deleting extra cells below data

    I have seen it and used it, but cannot find my way back. It is the tool that deletes the rows below the data and lightens the load of the worksheet.

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,002
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Deleting extra cells below data

    Quote Originally Posted by cherokee View Post
    I have seen it and used it, but cannot find my way back. It is the tool that deletes the rows below the data and lightens the load of the worksheet.
    Assuming you want to clear all formatting outside the used range to reduce file size, try this.
    Code:
    Sub RemoveFormatOutsideUsedRange()
    Dim ws As Worksheet, myCell As Range
    For Each ws In ActiveWorkbook.Worksheets
        Set myCell = lastCell(ws)
        Range(myCell.Columns(1).Offset(0, 1), myCell.Columns(1).Offset(0, 1).End(xlToRight)) _
        .EntireColumn.Clear
        Range(myCell.Rows(1).Offset(1, 0), myCell.Rows(1).Offset(1, 0).End(xlDown)).EntireRow.Clear
    Next ws
    End Sub
    Function lastCell(ws As Worksheet) As Range
    Dim LastRow&, LastCol%
    On Error Resume Next
    With ws
        LastRow& = .Cells.Find(What:="*", _
          SearchDirection:=xlPrevious, _
          SearchOrder:=xlByRows).Row
        LastCol% = .Cells.Find(What:="*", _
          SearchDirection:=xlPrevious, _
          SearchOrder:=xlByColumns).Column
      End With
    If Err.Number <> 0 Then 'worksheet is empty
       MsgBox ("Worksheet " & ws.Name & " is empty (all cells are blank).")
       Set lastCell = ws.Cells(1, 1)
    Else
      Set lastCell = ws.Cells(LastRow&, LastCol%)
    End If
    End Function
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular
    Join Date
    Apr 2012
    Location
    GA
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting extra cells below data

    Thanks, I will try this. I have five workbooks that are linked. The reason for five is that they are so large. You can not open more that two at a time. You can take a lunch break while they are opening. Then the problem of making an entry in the main workbook and opening each of the others (one at a time) for every thing to update. All the links are just (=).

    Your quote fits me perfectly.

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,002
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Deleting extra cells below data

    Quote Originally Posted by cherokee View Post
    Thanks, I will try this. I have five workbooks that are linked. The reason for five is that they are so large. You can not open more that two at a time. You can take a lunch break while they are opening. Then the problem of making an entry in the main workbook and opening each of the others (one at a time) for every thing to update. All the links are just (=).

    Your quote fits me perfectly.
    The code I posted will help trim file size if the size is the result of "File Bloat". This occurs, for example, when folks want to highlight a row(s) with color fill and select the entire row rather than the data-portion of the row. Similarly, for borders, etc.

    If will not help if the file size results from legitimate data volume and/or formula load (especially if there is a preponderance of array formulas).
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    Board Regular
    Join Date
    Apr 2012
    Location
    GA
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting extra cells below data

    The files are free of "File Bloat", but far to the extreme of a preponderance of array formulas. The song "Addicted to Love" is renamed for me, "Addicted to Array Formulas". I loaded one of the large files (before splitting them) on my son's loaded MAC. I knew Windows would not allow the use of less than 3G of my memory. It crashed while trying to open the file. That stopped his blowing of the MAC horn.

    When using the macro, I do get a screen saying, Cannot change part of merged cell.

    Thanks

  6. #6
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,002
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Deleting extra cells below data

    Here's a link to a lengthy, but very good article on improving performance in Excel. It may offer some tips applicable to your files. Improving Performance in Excel 2007
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  7. #7
    Board Regular
    Join Date
    Apr 2012
    Location
    GA
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting extra cells below data

    I certainly have time to read the article while files are booting. I usually do online puzzles while I am waiting. Thanks very much.

    I ran the macro in a small file not having any merged cells. The file starting size was 166KB and after running the Macro, the end results was 60KB. The file that I have trouble with are 61,990KB.

    Thanks again for your insight

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,421
    Post Thanks / Like
    Mentioned
    79 Post(s)
    Tagged
    16 Thread(s)

    Default Re: Deleting extra cells below data

    Does this thread actually have anything to do with the Easy-XL Add-In, which is the forum it is located in?

    If not, I will move it to the general 'Excel Questions' forum.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #9
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,002
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Deleting extra cells below data

    Quote Originally Posted by Peter_SSs View Post
    Does this thread actually have anything to do with the Easy-XL Add-In, which is the forum it is located in?

    If not, I will move it to the general 'Excel Questions' forum.
    Excel Questions seems more appropriate to me.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  10. #10
    Board Regular
    Join Date
    Apr 2012
    Location
    GA
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting extra cells below data

    It is related to XL. I remembered at one time seeing deleting blank rows, but could not remember where. Days after the post, I found it under "Query Sheet"; "Query by formula" and then you get the splash screen about deleting blank rows under your data.

    Thanks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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