Excel 2010: My Sub is Too Slow; How Can I Speed This Up?

[ceddins]

Good afternoon, tek-tip folks.

I have this relatively small section of code (seen below) that takes 32.4 seconds to run. I'd like to speed it up, but I've improved it as much as I can. Can any experts out there give me some suggestions?

Part of the problem is that the raw data I'm formatting is exported from the system wacky. Every 6th row starting from row 5 contains the data that I need. The other rows are full of blank cells and spaces and other useless data. Is there a better way to do this?

'This section of the code formats the raw data file
    Dim RowVar As Long
    
    'delete all unnecessary rows
    Sheet.Cells(543, 1).EntireRow.Delete
    
    For RowVar = 542 To 3 Step -1
        If (539 - RowVar) Mod 6 <> 0 Then
            Sheet.Cells(RowVar, 1).EntireRow.Delete
        End If
    Next

Thank you for taking a look.

 

[mintjulep]

Instead of deleting what you don't need, how about picking out what you do need and writing it to a seperate sheet. Then delete the entire wacky sheet.

 

[ceddins]

Hi mintjulep,

I tried your tip and the code runs a lot faster. Thank you.
Here's the new code:

'This section of the code formats the raw data

    Dim RowVar As Long
    Dim UsedCols As Long
    Dim Sheet2 As Worksheet
    Dim CountVar As Long
    DateBegin = Sheet.Cells(3, 1).Value
    UsedRows = Sheet.UsedRange.Rows.Count
    UsedCols = Sheet.UsedRange.Columns.Count
    CountVar = 1

    Set Sheet2 = Worksheets.Add
'change in code Aug 9, 2013: instead of deleting all the unnecessary rows, I copy the necessary rows to a new sheet
    
    For RowVar = 3 To 542
        If (539 - RowVar) Mod 6 = 0 Then
            CountVar = CountVar + 1
            Sheet2.Range(Sheet2.Cells(CountVar, 1), Sheet2.Cells(CountVar, UsedCols)).Value = Sheet.Range(Sheet.Cells(RowVar, 1), Sheet.Cells(RowVar, UsedCols)).Value
        End If
    Next
    
    Sheet2.Range(Sheet2.Cells(1, 1), Sheet2.Cells(1, UsedCols)).Value = Sheet.Range(Sheet.Cells(2, 1), Sheet.Cells(2, UsedCols)).Value

 

[DaveInIowa]

Would performing a Union and doing a single Delete speed things up?

Dim rgChunk As Range
Dim rgToDelete As Range
    
Set rgChunk = Sheet.Range("6:10")
Set rgToDelete = rgChunk
    
While (rgChunk.Row < 541)
    Set rgToDelete = Union(rgToDelete, rgChunk)
    Set rgChunk = rg.Offset(6)
Wend
    
rgToDelete.Delete

 

[mintjulep]

If you already know the useful data starts in row 5 and is every 6th row thereafter:

    For RowVar = 5 to 542 step 6
         Sheet2.Range(Sheet2.Cells((RowVar+1)/6, 1), Sheet2.Cells((RowVar+1)/6, UsedCols)).Value = Sheet.Range(Sheet.Cells(RowVar, 1), Sheet.Cells(RowVar, UsedCols)).Value
    Next RowVar