File Size-HUGE! How can I find last line of my data to insert?

  • This is my guess to my problem.
    I have data theat is in one sheet.
    I have written some code to import data, and place it in row 5000, then I do a sort to consolidate the data. I then run a combine routine to consolidate the data. The cells in between rows 100-5000 appear to be empty, but I think something must be there. I do not know what because all I do is insert maybe 20 rows at row 5000. Reason is I do not currently know how to determine the current last row, then insert. Maybe this is a better approach.

    I am including the Excel file.

    Again, this group is wonderful, and I appreciate any support.

  • The line

    Debug.Print ActiveSheet.Range("A65536").End(xlUp).Row + 1

    will print the first row in which column A has no data, looking upward from the bottom of the worksheet. It will work unless all cells in column A have data, in which case it returns the value 2.

    Hope this helps.


  • Hmm, gives me sommethng to go on. I don't need to print, I just want to delete empty data in rows. When I manually delete rows 2-10000, the file size drps back down to like 80K.

  • You can use the formula after the Debug.Print to return the number of that first empty row. I just put in the Debug.Print to make it an actual statement, since everything to the right of it represents a value. It seemed like you've got empty rows from 2 to 10000 because you don't initially know how long your existing file is and so you're adding data beginning with 10000. If that's true, you can add data starting with row (ActiveSheet.Range("A65536").End(xlUp).Row + 1) and not end up with those blank rows.

    Hope this helps


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!