+ Reply to Thread
Results 1 to 4 of 4

Code running slowly

Hybrid View

  1. #1
    Registered User
    Join Date
    05-26-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    65

    Code running slowly

    All,

    I have the code below designed to remove any picture objects, reset the font, and delete some blank cells and some columns. I have it looping in a folder with about 52k workbooks that need to be modified. Unlike my other macros,this one has been running for 3 days and still isn't done. Is there something in the code that could be causing delays? The sheets are less than 100k in size each.

    Thanks!

    Sub New_Clean()
    '
    ' Macro2 Macro
    '
    Dim PathName As String
    Dim FileName As String
    Dim CurrentWB As Workbook
    Dim Pic As Object
    
    PathName = "C:\BoxScoreTest\"
    FileName = Dir(PathName & "*.xls")
    Do While FileName <> ""
      Set CurrentWB = Workbooks.Open(PathName & FileName)
      With CurrentWB.ActiveSheet
        .Rows("1:30").Delete Shift:=xlUp
     With .UsedRange.Font
          .Name = "Verdana"
          .Size = 10
          .Strikethrough = False
          .Superscript = False
          .Subscript = False
          .OutlineFont = False
          .Shadow = False
          .TintAndShade = 0
          .ThemeFont = xlThemeFontNone
          .ColorIndex = xlAutomatic
              
        ActiveWindow.SmallScroll Down:=-120
        Range("A1:BR500").Select
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Selection.Delete Shift:=xlToLeft
     
        Columns("I:BX").Select
        Selection.Delete Shift:=xlToLeft
        
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    For Each Pic In ActiveSheet.Pictures
    Pic.Delete
    Next Pic
    
        End With
        End With
    
    CurrentWB.Close True
    FileName = Dir()
    
    Loop
    End Sub

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Code running slowly

    Hi Sweepin,

    I altered your code a little with a couple of comments - see if it's any quicker:

    Sub New_Clean()
    '
    ' Macro2 Macro
    '
    Dim PathName As String
    'Dim FileName As String   could be risky as FileName may be reserved word
    Dim NewBook As String
    Dim CurrentWB As Workbook
    Dim Pic As Object
    
    PathName = "C:\BoxScoreTest\"
    NewBook = Dir(PathName & "*.xls")
    Do While NewBook <> ""
      Set CurrentWB = Workbooks.Open(PathName & NewBook, 0) '0 here knocks off the update links
      With CurrentWB.ActiveSheet
        .Rows("1:30").Delete Shift:=xlUp
     With .UsedRange.Font
          .Name = "Verdana"
          .Size = 10
          .Strikethrough = False
          .Superscript = False
          .Subscript = False
          .OutlineFont = False
          .Shadow = False
          .TintAndShade = 0
          .ThemeFont = xlThemeFontNone
          .ColorIndex = xlAutomatic
              
        Range("A1:BR500").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
     
        Columns("I:BX").Select
        Selection.Delete Shift:=xlToLeft
        
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    For Each Pic In ActiveSheet.Pictures
    Pic.Delete
    Next Pic
    
        End With
        End With
    
    CurrentWB.Close True
    NewBook = Dir()
    
    Loop
    End Sub
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    05-26-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    65

    Re: Code running slowly

    Yup, that's running much faster. Thanks!

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Code running slowly

    Fantastic! Thanks for the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Code running slowly any way of speeding up processing time
    By boomboomblock in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2013, 09:09 AM
  2. [SOLVED] Formatting sheets with macro running very slowly
    By nickmax1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-21-2012, 10:04 AM
  3. Code running slowly
    By rhudgins in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 02-01-2011, 11:08 AM
  4. Paste code running extremely slowly...
    By KR in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-03-2005, 11:05 AM
  5. Macros in Excel 2000 running very slowly
    By cottage6 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-06-2005, 06:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1