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

martint86

New Member
Joined
Jan 9, 2014
Messages
1
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:

Code:
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

BrianMH

Well-known Member
Joined
Mar 26, 2008
Messages
1,490
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,600
Messages
5,597,114
Members
414,125
Latest member
iQQ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top