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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,082
Messages
5,857,251
Members
431,866
Latest member
Paula777

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