Trim/Clean Rows faster in VBA

[mnh]

Hello -
I have the following code, but it takes a long time to run. Can someone help me shorten it/make it run faster?
Thank You!

 Dim ws As Worksheet
 Dim lastRow As Long
 Dim i As Long
            
    ' Specify the worksheet
    Set ws = ThisWorkbook.Sheets("IMPORT_RATES")
            
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            
    ' Loop through rows and trim/clean cells in columns B through F if there's data
    For i = 1 To lastRow
    
        ' Check if there's data in column A
        If Not IsEmpty(ws.Cells(i, 1).Value) Then
            ' Trim and clean cell in column A
            ws.Cells(i, 1).Value = WorksheetFunction.Trim(ws.Cells(i, 1).Value)
        End If
        
        ' Check if there's data in column B
        If Not IsEmpty(ws.Cells(i, 2).Value) Then
            ' Trim and clean cell in column B
            ws.Cells(i, 2).Value = WorksheetFunction.Trim(ws.Cells(i, 2).Value)
        End If
                
        ' Check if there's data in column C
        If Not IsEmpty(ws.Cells(i, 3).Value) Then
            ' Trim and clean cell in column C
            ws.Cells(i, 3).Value = WorksheetFunction.Trim(ws.Cells(i, 3).Value)
        End If
        
        ' Check if there's data in column D
        If Not IsEmpty(ws.Cells(i, 4).Value) Then
            ' Trim and clean cell in column D
            ws.Cells(i, 4).Value = WorksheetFunction.Trim(ws.Cells(i, 4).Value)
        End If
     
    Next i

 

[KOKOSEK]

Dim ws As Worksheet
 Dim lastRow As Long
 Dim i , j As Long
           
    ' Specify the worksheet
    Set ws = ThisWorkbook.Sheets("IMPORT_RATES")
           
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
           
    ' Loop through rows and trim/clean cells in columns B through F if there's data
    For i = 1 To lastRow
      For j=1 to 4

        If Not IsEmpty(ws.Cells(i, j).Value) Then ws.Cells(i, j).Value = WorksheetFunction.Trim(ws.Cells(i, j).Value)

       Next j
    Next i

Shorter - yes, faster - probably a bit.

 

[Carl Stephens]

Here's the shortened and optimised version of your VBA code to improve performance:

Sub CleanAndTrimData()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range

    ' Specify the worksheet (adjust sheet name if needed)
    Set ws = ThisWorkbook.Sheets("IMPORT_RATES")

    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Define data range (columns B to F) - adjust if needed
    Set dataRange = ws.Range("B1:F" & lastRow)

    ' Clean and trim data in one go (avoiding individual cell references)
    With dataRange
        .Value = Application.Trim(.Value)
    End With

End Sub

 

[mnh]

This work

Here's the shortened and optimised version of your VBA code to improve performance:

Sub CleanAndTrimData()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range

    ' Specify the worksheet (adjust sheet name if needed)
    Set ws = ThisWorkbook.Sheets("IMPORT_RATES")

    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Define data range (columns B to F) - adjust if needed
    Set dataRange = ws.Range("B1:F" & lastRow)

    ' Clean and trim data in one go (avoiding individual cell references)
    With dataRange
        .Value = Application.Trim(.Value)
    End With

End Sub[/
[/QUOTE]

Here's the shortened and optimised version of your VBA code to improve performance:

Sub CleanAndTrimData()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range

    ' Specify the worksheet (adjust sheet name if needed)
    Set ws = ThisWorkbook.Sheets("IMPORT_RATES")

    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Define data range (columns B to F) - adjust if needed
    Set dataRange = ws.Range("B1:F" & lastRow)

    ' Clean and trim data in one go (avoiding individual cell references)
    With dataRange
        .Value = Application.Trim(.Value)
    End With

End Sub

This worked perfectly - thank you so much! You rock!