Ugly slow code....

**Slyone2** · 08-15-2010, 04:11 PM

Works, but really slow.....

      Sheets("TS1").Rows("3:3").Delete
            Sheets("TS2").Rows("3:3").Delete
            Sheets("TS3").Rows("3:3").Delete
            Sheets("TS4").Rows("3:3").Delete
            Sheets("ML13").Rows("3:3").Delete
            Sheets("ML24").Rows("3:3").Delete
            Sheets("SL13").Rows("3:3").Delete
            Sheets("SL24").Rows("3:3").Delete
            Sheets("TV1").Rows("3:3").Delete
        
            Sheets("TS1").Rows("44:44").Insert
            Sheets("TS2").Rows("44:44").Insert
            Sheets("TS3").Rows("44:44").Insert
            Sheets("TS4").Rows("44:44").Insert
            Sheets("ML13").Rows("44:44").Insert
            Sheets("ML24").Rows("44:44").Insert
            Sheets("SL13").Rows("44:44").Insert
            Sheets("SL24").Rows("44:44").Insert
            Sheets("TV1").Rows("44:44").Insert

I tried Array it didnt work for me, Any ideas for faster cleaner code?

**snb** · 08-15-2010, 04:21 PM

Sub snb()
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    
    For Each sh In Sheets
        sh.Rows(3).Delete
    Next
    Sheets(1).Rows(44).Insert
    Sheets.FillAcrossSheets Sheets(1).Rows(44), xlFillWithAll
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
   
End Sub

**shg** · 08-15-2010, 04:24 PM

    Dim v As Variant
    
    Application.Calculation = xlCalculationManual
    For Each v In Array("TS1", "TS2", "TS3", "TS4", "ML13", "ML24", "SL13", "SL24", "TV1")
        Worksheets(v).Rows(3).Delete
        Worksheets(v).Rows(44).Insert
    Next v
    Application.Calculation = xlCalculationAutomatic

**Slyone2** · 08-15-2010, 04:46 PM

thanks, its didnt help improve .....slowness.....loooking for source......

**Slyone2** · 08-15-2010, 05:14 PM

The prob is $1:200 reference back to the sheets just changed.
Even, insert/ delete should keep range intact.

Excel slows. iguess checking everything.

Any thoughts?

**Slyone2** · 08-16-2010, 07:36 PM

any other ideas?

**TryToExcel** · 08-16-2010, 08:02 PM

SlyOne2,

When you say "slow" what time frame are talking about (10 seconds, longer?) so we can have an idea what we are trying to improve upon. Also, how many average columns and rows are there per sheet? Do you have a lot of formulas? As you see "snb" turned off the calculations to speed things up. Give us more to go on.

Danny
Or just an average are

**Slyone2** · 08-16-2010, 08:13 PM

Timing:
When I comment out /loop around my ugly or Shg code it take 25 seconds.....
When use my ugly or Shg code it take 3-4 minutes...
Rows start from 4 goto 200 per sheets. columns around 200 per sheets.

The only thing different is deleting and insert rows, which seems to throw Hlookup into tizzy.
The sheets are tables, since old rows in tables are not used, I want to delete to keep xls small.

Hlookup use $1:$200 to find row.