1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Large Excel File: Copying & Pasting

Discussion in 'Business Applications' started by Addiead0725, Feb 15, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Addiead0725

    Addiead0725 Thread Starter

    Joined:
    Jun 14, 2004
    Messages:
    268
    Good morning,

    My apologies in advance for posting a question that has been answered many times here and on the interwebs already, but the answers I find are mostly the same and I need further clarification, if possible.

    I work at as a tech at a law firm and I'm having an issue where 2 users are using a shared excel workbook to copy and paste data into from miscellaneous client websites, such as client names, loan numbers, etc.

    Every once and a while, when one of the users is done making their changes and saves their work, their workbook size will jump anywhere between 20MB and 250MB in size from the original, which is usually in the lower KB range. All answers I've found include copying and pasting the content into a new spreadsheet, recreating the document, finding the last used cell with Ctrl+End and clearing the blank spaces, and removing text styles like boldness and colors. All these solutions do work, but it's hard to keep telling an attorney to do these things without them biting your head off about it - because this does happen multiple times a day on this one spreadsheet. It is, in fact, due to blank cells, as the last active cell usually hits the 1,000,000 number range when the file size jumps up.

    I heard there's away to lock a spreadsheet to a predetermined cell count, but I'm still researching this method. If anyone can provide me further explanation on why this happens and how to prevent it, please do. Thanks in advance.
     
  2. Addiead0725

    Addiead0725 Thread Starter

    Joined:
    Jun 14, 2004
    Messages:
    268
  3. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    1. Is it just data or does it include images and other objects?
    2. Are all the cells joined in a single table without gaps?
     
  4. Addiead0725

    Addiead0725 Thread Starter

    Joined:
    Jun 14, 2004
    Messages:
    268
    From what I've been told, it seems to be information (loan numbers, names, etc.) copied and pasted from multiple website pages and/or tables. The cells themselves are joined in a single excel table with no visible gaps between the data entered.
     
  5. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Code:
    Sub test()
        Dim lastCell As Range
        Dim rowCount As Long
        Dim colCount As Long
        Dim ws As Worksheet
        
        Set ws = ActiveSheet
        
        If ws.Shapes.count Then 'remove objects
            ws.Shapes.SelectAll
            Selection.Delete
        End If
        
        With ws.Cells.Font 'remove formatting
            .ColorIndex = xlColorIndexAutomatic
            .Bold = False
        End With
        
        ws.Cells.Replace " ", "", xlWhole 'remove space
        
        ActiveSheet.UsedRange
        
        Set lastCell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) 'get last cell
        
        'remove empty cells
        Do Until (rowCount > 0 And colCount > 0) Or lastCell.Address = "$A$1"
            rowCount = Application.CountA(lastCell.EntireRow)
            colCount = Application.CountA(lastCell.EntireColumn)
            Set lastCell = lastCell.Offset(1 * (rowCount = 0), 1 * (colCount = 0))
        Loop
        
        Rows(lastCell.Row + 1 & ":" & Rows.count).Delete
        Range(Cells(1, lastCell.Column + 1), Cells(Rows.count, Columns.count)).Delete
    End Sub
    
     
  6. Addiead0725

    Addiead0725 Thread Starter

    Joined:
    Jun 14, 2004
    Messages:
    268
    Thank you, Garfield - I will try implementing this code. While waiting for responses though, I was able to 'hide' the excess cells, which I think is preventing unwanted data input. If the user calls me back with the recurring issue after my bandaid, I will try using this VBA. Thanks for your help.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1089649

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice