Fast Way to Normalize Data with VBA (Excel)

Question

I'm currently trying to normalize data with VBA in Excel. Therefore, my workbook imports several csv files and wrote them in different worksheets all of them are built like this.

• First row: Header
• First column: x-Axis (for plotting)
• Second column to nth column: y-values

Now I want to normalize all columns from 2 to n (dividing by maximum value of each column). Here is the function I'm using so far:

Sub NormalizeData(dataName)

cs = Worksheets(dataName).UsedRange.SpecialCells(xlCellTypeLastCell).Column
rs = Worksheets(dataName).UsedRange.SpecialCells(xlCellTypeLastCell).Row

For col = 2 To cs
    maxValue = Application.WorksheetFunction.Max(Worksheets(dataName).Columns(col))

    For r = 2 To rs
        Worksheets(dataName).Cells(r, col) = Worksheets(dataName).Cells(r, col) / maxValue
    Next r
Next col 
End Sub

This approach works, but because of the amount of data, it's very slow. Is there any way to increase the speed? I already switched of the screen update.

Thanks you very much for your help!!!

Answer 1

Here is a sub that normalizes the numbers in a rectangular range. You can decide on what range you want to normalize and then pass that range to this sub:

Sub NormalizeRange(R As Range)
    'assumes that R is a rectangular range
    'will throw an error if any column has max 0

    Dim vals As Variant, maxes As Variant
    Dim i As Long, j As Long, m As Long, n As Long

    m = R.Rows.Count
    n = R.Columns.Count
    ReDim maxes(1 To n)

    With Application.WorksheetFunction
        For i = 1 To n
            maxes(i) = .Max(R.Columns(i))
        Next i
    End With

    vals = R.Value
    For i = 1 To m
        For j = 1 To n
            vals(i, j) = vals(i, j) / maxes(j)
        Next j
    Next i
    R.Value = vals
End Sub

This will be more efficient than what you currently has since it moves values between the spreadsheet and VBA in bulk transfers rather than via a large number of individual read/writes. It also avoids things like screen-updating issues and intermediate recalculation of functions depending on these values.

Answer 2

Use another sheet and PasteSpecial. Assuming ws1 contains your data and ws2 is currently unused:

with ws2.Range(.Cells(2,2), .Cells(rs, cs))
    .value = maxValue
    .copy
end with
ws1.Range(.Cells(2,2), .Cells(rs, cs)).PasteSpecial _
Operation:=xlPasteSpecialOperationDivide
Application.CutCopyMode = False