Delete Column Macro running slow after moving file type from xls to xlsm

[martint86]

Hi everyone,

First of all would just like to say a massive thank you to everyone who contributes to this forum, I've been relying on it for a couple of years to get me through all kind of VBA questions and issues and usually I can find an answer in seconds, so thanks!

However run into a bit of a problem that has completely stumped me, I have this code which is part of a Macro I use daily:

Sub DeleteEmptyCells()
    ''''''''To delete all non entered rows and formatting
    ActiveCell.Rows("1:1").EntireRow.Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    
    Rows("1:10").Select
    Selection.Delete Shift:=xlUp
    ''''''''To delete all non entered rows and formatting
    Columns("T:IV").Select
    Selection.Delete Shift:=xlToLeft
                
    Columns("M:M").Select
    Selection.Delete Shift:=xlToLeft
                
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft
     
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
End Sub

It's fairly straight forward if a little clumsy (it's not my code but I've inherited it and just not got round to tidying it up) anyway...

The file was in 2003 format and worked fine, I've recently re-saved it as a .xlsm file as I wanted it to support some formatting only available in 2007 and later, now when I run the macro it grinds to a halt when it reaches the bit of code above.

I don't know if it has anything to do with the extra rows/columns available in Excel 2007 and later or what, I found a thread about reducing the number of rows/columns but it doesn't seem to have any effect.


http://www.mrexcel.com/forum/excel-...ook-less-rows-25-000-instead-1-048-576-a.html


If anyone has any ideas on what I can do to sort this, I would be very grateful,

Thanks

Martin

 

[BrianMH]

Is the activecell a blank cell? In other words are you deleting all the rows below the bottom of your data? If so what column is this in? I'm going to assume A and you can change this.

You don't need to select the cells at all.

Sub DeleteEmptyCells()
    On Error GoTo errHandler
    Application.ScreenUpdating = False
    Dim r As Range


    With ActiveSheet
            'get the bottom cell of the data on the activeworksheet.  I personally would define the worksheet instead.
        Set r = .Range("A" & .Rows.Count).End(xlUp).Offset(1)
        Set r = Range(r, r.End(xlDown)).EntireRow
        r.Clear
        .Rows("1:10").Delete
        .Range("T:IV").Delete
        .Range("M:M").Delete
        .Range("K:K").Delete
        .Range("J:J").Delete
    End With
Exiter:
    Application.ScreenUpdating = True
errHandler:
    MsgBox Err.Number & " - " & Err.Description
    GoTo Exiter
End Sub