Memory used jumped from 80m to 1.6G from Deleteing method

[Ceeyee]

Please help!

I have following VBA code

Sub DeletefromRow5()

Rows(5).Select
Range(Selection, Selection.End(xlDown)).Delete Shift:=xlUp

End Sub


After I run the code my memory usage jump from 51M to 1.6G!! and the program crashed.

Can anyone take a look at my Excel file to see what's happening? I made the file very simple and contain only the above code.

http://dl.dropbox.com/u/9846094/Public/workbook1.xlsm



Thanks a lot!!

 

[Ceeyee]

I need to find out which cell(s) are causing the problem, because this simple file is from another much larger file which doesn't allow to start from scratch.

Can anyone tell me how to identify where the cells/formats corrupt in the workbook?

Thanks a lot!!

 

[Weaver]

In the past and again with older versions of Excel, I've solved issues such as these by deleting all the empty rows/columns in a workbook and then saving it with a new filename.

 

[tweedle]

The file I d/l (first one) reports over 199M cells....is that right?
something (data transfer mechanism?) isn't clearing/reusing cell-areas.

xl Used Cells: 199,229,440
xl Used Rows: 1,048,576
xl Used Cols: 190
dat Used Rows: 16
dat Used Cols: 2

Sub foo()
UsedC = ActiveSheet.UsedRange.Cells.Count
xlUR = ActiveSheet.UsedRange.Rows.Count
xlUC = ActiveSheet.UsedRange.Columns.Count
LDR = Range("A" & Rows.Count).End(xlUp).Row
LDC = Range(Cells(1, Columns.Count).Address).End(xlToLeft).Column
Debug.Print "xl Used Cells: "; Format(UsedC, "#,##0")
Debug.Print "xl Used Rows: "; Format(xlUR, "#,##0")
Debug.Print "xl Used Cols: "; Format(xlUC, "#,##0")
Debug.Print "dat Used Rows: "; Format(LDR, "#,##0")
Debug.Print "dat Used Cols: "; Format(LDC, "#,##0")
End Sub

 

[Ceeyee]

But the problem is that I can't delete even one row now because it leads to a crash.

In the past and again with older versions of Excel, I've solved issues such as these by deleting all the empty rows/columns in a workbook and then saving it with a new filename.

 

[Ceeyee]

From the numbers it appears that there are 190 columns full of data.
Is there anyway to programatically clear them?
Thanks.

The file I d/l (first one) reports over 199M cells....is that right?
something (data transfer mechanism?) isn't clearing/reusing cell-areas.

xl Used Cells: 199,229,440
xl Used Rows: 1,048,576
xl Used Cols: 190
dat Used Rows: 16
dat Used Cols: 2

Sub foo()
UsedC = ActiveSheet.UsedRange.Cells.Count
xlUR = ActiveSheet.UsedRange.Rows.Count
xlUC = ActiveSheet.UsedRange.Columns.Count
LDR = Range("A" & Rows.Count).End(xlUp).Row
LDC = Range(Cells(1, Columns.Count).Address).End(xlToLeft).Column
Debug.Print "xl Used Cells: "; Format(UsedC, "#,##0")
Debug.Print "xl Used Rows: "; Format(xlUR, "#,##0")
Debug.Print "xl Used Cols: "; Format(xlUC, "#,##0")
Debug.Print "dat Used Rows: "; Format(LDR, "#,##0")
Debug.Print "dat Used Cols: "; Format(LDC, "#,##0")
End Sub

 

[tweedle]

Try the below on a test workbook.
The columns go pretty quick
The rows take a while longer.

Chose to loop through for deletion as bulk deletions readily crash.

Dim ws As Worksheet
Const FixCols = True   'True to perform Column Deletion
Const FixRows = False   'True to perform Row Deletion
Const RowInterval = 100 'How many rows to delete at each command
Const ShowWork = True 'Indicates if screen should update [False=faster]
Sub FixIt()
    
    If Not ShowWork Then Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Set ws = ThisWorkbook.Sheets(1)
    UsedC = ws.UsedRange.Cells.Count
    xlUR = ws.UsedRange.Rows.Count
    xlUC = ws.UsedRange.Columns.Count
    LDR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    LDC = ws.Range(Cells(1, Columns.Count).Address).End(xlToLeft).Column
If FixCols + FixRows = 0 Then
    ReportToDebug ws, "Just Reporting"
End If
    
    If FixCols Then
    ReportToDebug ws, "BEFORE Columns"
        ws.Columns(xlUC + 1).ColumnWidth = 2
        ws.Columns(xlUC + 1).Interior.ColorIndex = 1
        For ColIdx = xlUC To (LDC + 1) Step -1
            ws.Cells(1, ColIdx).Activate
            Application.StatusBar = "Col: " & RowIdx
            ws.Columns(ColIdx).Delete
            DoEvents
        Next ColIdx
        ws.Columns(LDC + 1).Delete
    ReportToDebug ws, "AFTER Columns"
    End If

    If FixRows Then
    ReportToDebug ws, "BEFORE Rows"
        For RowIdx = xlUR To (LDR + 1) Step -RowInterval
            If RowIdx < LDR Then Exit For
            ws.Cells(RowIdx, 1).Activate
            Application.StatusBar = "Row: " & RowIdx
            ws.Rows(RowIdx - (RowInterval - 1)).Resize(RowInterval).Delete
            DoEvents
        Next RowIdx
    ReportToDebug ws, "AFTER Rows"
    End If
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Sub ReportToDebug(ws As Worksheet, desc As String)
'Reports worksheet range used
Debug.Print "========================"
Debug.Print desc
UsedC = ws.UsedRange.Cells.Count
xlUR = ws.UsedRange.Rows.Count
xlUC = ws.UsedRange.Columns.Count
LDR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
LDC = ws.Range(Cells(1, Columns.Count).Address).End(xlToLeft).Column
Debug.Print "xl Used Cells: "; Format(UsedC, "#,##0")
Debug.Print "xl Used Rows: "; Format(xlUR, "#,##0")
Debug.Print "xl Used Cols: "; Format(xlUC, "#,##0")
Debug.Print "dat Used Rows: "; Format(LDR, "#,##0")
Debug.Print "dat Used Cols: "; Format(LDC, "#,##0")
End Sub

 

[Ceeyee]

Thank you so much.
Let me try the code.