Workbook increases in size after running this macro

[Paul B]

Sub CopyData()
Dim lrow As Long
 
lrow = Sheet7.Cells(Rows.Count, 1).End(xlUp).Row
  
Sheet7.Range("A2:B" & lrow & "").SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

End Sub

I am using this to copy the filtered data to another sheet

At this time lrow =350

Workbook starts out at 440kb, after running the code 5 times workbook is 1,050kb, sounds a little high but may be ok just added 1,750 rows of data.

I delete the data that was added in sheet3 columns A:B file size is now 964kb, thought it would be around 440 again.

I ran the code 5 more times and deleted all the data that was put in, file size now is 1,488kb

The more I run the code the bigger the file gets.

Checked where Excel thinks the last row in sheet3 is an it is H1, that is where it should be.

I deleted the sheet, sheet3, added another sheet to take its place and the file size is now back to 440kb.

Any ideas on why and how I can keep this from happening?
Excel 2003

Thanks Paul

 

[JoeMo]

As you've noted, you can't prevent an increase in size if the macro is adding data to the workbook, but from your observations it appears that Excel is not resetting the used range when you delete data. You can use code to do this. Here's some code I use that resets the used range on every sheet in a workbook. (This is not my code and I have lost the name of its creator.)

Sub DeleteUnusedRowsColumns()
'Determines real used range and deletes all rows and columns
'outside that range. May not work if there are merged cells outside
'the real used range.

Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
Dim wCt As Long


For Each wks In ActiveWorkbook.Worksheets
  With wks
    Application.StatusBar = "CLEANING " & wks.Name
    myLastRow = 0
    myLastCol = 0
    Set dummyRng = .UsedRange
    On Error Resume Next
    myLastRow = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, lookat:=xlWhole, _
        searchdirection:=xlPrevious, _
        searchorder:=xlByRows).Row
    myLastCol = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, lookat:=xlWhole, _
        searchdirection:=xlPrevious, _
        searchorder:=xlByColumns).Column
    On Error GoTo 0

    If myLastRow * myLastCol = 0 Then
        .Columns.Delete
    Else
        .Range(.Cells(myLastRow + 1, 1), _
          .Cells(.Rows.Count, 1)).EntireRow.Delete
        .Range(.Cells(1, myLastCol + 1), _
          .Cells(1, .Columns.Count)).EntireColumn.Delete
    End If
  End With
  wCt = wCt + 1
Next wks
Application.StatusBar = False
MsgBox wCt & " worksheets were cleaned. Save this file to complete the changes."
End Sub

 

[Paul B]

JoeMo, thanks but as stated the used range is right, ran the code anyway and it did not change the file size.

 

[JoeMo]

If it didn't change the size even after you deleted the data and then ran the code then it's not a used range reset problem.

 

[Paul B]

I don't think it is a problem with the used range but don't know what else might be causing it

 

[Paul B]

After MUCH testing it looks like it had something to do with the data itself, it was copied from the web but put in as text only, everything look good but something was not right with it, I copied the date to notepad and put it back in but that did not help. I had to copy the data to notepad delete the worksheet, insert another worksheet and paste the data in it and it works fine now.
From now on I will run it thought notepad first!