Removing Inefficent Code

**hchurch** · 11-22-2010, 10:53 AM

I have several instances of code that repeats for different variables, cells, and ranges. Being new to vba programming I need help in trying to condense and streamline the code that I am using if possible. This first code sample is repeated 5 times:

dblMaxSpec = 0
        dblMaxSpec = dblBasisWeightMax
        dblMinSpec = 0
        dblMinSpec = dblBasisWeightMin
        
        With ActiveWorkbook.Worksheets(2)
        
            intLastRow = 0
            intLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
            Set rCalculate = .Range(.Cells(3, 4), .Cells(intLastRow, 4))
        
            Call CalculateAveragesStDev
            
        End With

        .Cells(20, 2) = dblCalcAvg
        .Cells(20, 3) = dblCalcStDev
        .Cells(20, 4) = intCountMin
        .Cells(20, 5) = intCountMax
        
        With ActiveWorkbook.Worksheets(2)
        
            Set rCalculate = .Range(.Cells(3, 10), .Cells(intLastRow, 10))
        
            Call CalculateAveragesStDev
            
        End With

        .Cells(29, 2) = dblCalcAvg
        .Cells(29, 3) = dblCalcStDev
        .Cells(29, 4) = intCountMin
        .Cells(29, 5) = intCountMax

This second code sample is repeated 10 times:

dblMaxSpec = 0
        dblMaxSpec = dblBasisWeightMax
        dblMinSpec = 0
        dblMinSpec = dblBasisWeightMin
        
        With ActiveWorkbook.Worksheets(2)
        
            intLastRow = 0
            intLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
            Set rCalculate = .Range(.Cells(3, 4), .Cells(intLastRow, 4))
        
            Call CalculateAveragesStDev
            
        End With

        .Cells(20, 2) = dblCalcAvg
        .Cells(20, 3) = dblCalcStDev
        .Cells(20, 4) = intCountMin
        .Cells(20, 5) = intCountMax
        
        With ActiveWorkbook.Worksheets(2)
        
            Set rCalculate = .Range(.Cells(3, 10), .Cells(intLastRow, 10))
        
            Call CalculateAveragesStDev
            
        End With

        .Cells(29, 2) = dblCalcAvg
        .Cells(29, 3) = dblCalcStDev
        .Cells(29, 4) = intCountMin
        .Cells(29, 5) = intCountMax

I am also attaching a copy of the entire workbook. The above code is in the Sheet1, worksheet activate sub.

**Living Knecht** · 11-22-2010, 11:55 AM

Hi,

the values for different sheets and cells You can hold in an array and then go through it using a For-Next cycle. But as for the different variables... I do not know, if it is possible. What about to use an array here, too, and instead of VarA, VarB... call them aVar(1), aVar(2)...?

**snb** · 11-24-2010, 12:30 PM

First have a look into 'passing arguments"

instead of

    Set rCalculate = .Range(.Cells(3, 4), .Cells(intLastRow, 4))
     Call CalculateAveragesStDev

you'd better use:

     CalculateAveragesStDev .Range(.Cells(3, 4), .Cells(intLastRow, 4))

And plunge into 'loops'

With ActiveWorkbook.Worksheets(2)
   x= .Cells(Rows.Count, 1).End(xlUp).Row
   for j=1 to 5
     CalculateAveragesStDev .Range(.Cells(3, 4+ 6*(j-1)), .Cells(x, 4+6*(j-1)))
     .Cells(20+9*(j-1), 2).resize(,4) = array(dblCalcAvg,dblCalcStDev,intCountMin, intCountMax)
   next
End With

**hchurch** · 11-24-2010, 02:14 PM

Thanks snb. That is getting closer to what I was looking for. However, your code iterates through columns 4, 10, 16, 22, 28:

With ActiveWorkbook.Worksheets(2)
   x= .Cells(Rows.Count, 1).End(xlUp).Row
   for j=1 to 5
     CalculateAveragesStDev .Range(.Cells(3, 4+ 6*(j-1)), .Cells(x, 4+6*(j-1)))
     .Cells(20+9*(j-1), 2).resize(,4) = array(dblCalcAvg,dblCalcStDev,intCountMin, intCountMax)
   next
End With

I need to process columns 4 & 10 as a set, then the next iteration needs to process columns 5 & 11, etc. Each step uses a different set of comparison variables for the intCountMin & intCountMax variables.

**hchurch** · 11-24-2010, 02:35 PM

Thanks snb. I figured out how to get my iterations to work the way I want.

With ActiveWorkbook.Worksheets(2)

    x = .Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 1 To 5
    
        For j = 1 To 2
        
            CalculateAveragesStDev .Range(.Cells(3, 4 + (i - 1) + 6 * (j - 1)), _
                .Cells(x, 4 + (i - 1) + 6 * (j - 1)))
        
        Next j
        
    Next i
    
End With

Appreciate the help. Now I can apply that to other procedures and greatly reduce the amount of coding in this workbook.