TRIM Function for only rows with yesterday's Date-CodePudding

I have this TRIM function running but to save time would like to only run it on the previous days rows, column G contains a Date.

Please can someone guide me to achieving this.

sub TrimText()
Dim lRow As Integer

With Worksheets("Data")
    lRow = .Range("A2").End(xlDown).Row
    
    For i = 2 To lRow
        .Cells(i, "A").Value = Trim(.Cells(i, "A").Value)
    Next i
End With
End Sub

CodePudding user response：

If you mean that there will be a date stored in column G, and if that date is yesterday when running - then the TRIM code should run on that row:

For i = 2 To lRow
    If .Cells(i, "G").Value = Int(Now()) - 1 Then
        .Cells(i, "A").Value = Trim(.Cells(i, "A").Value)
    End If
Next i

CodePudding user response：

If your doing this for the sake of speed/time, adding code to check the date isn't the way to go about it really.

However, assuming the date in column G is DD/MM/YYY (or whatever order your system uses in a Date formatted column), this will check the value in column G and skip if the date is yesterday (purely date, no time)

Sub TrimText()
Dim lRow As Integer

Dim Yesterday As Date
Dim Test As Date
Yesterday = DateAdd("d", -1, Now)
Test = DateSerial(Year(Yesterday), Month(Yesterday), Day(Yesterday))

With Worksheets("Data")
    lRow = .Range("A2").End(xlDown).Row
    
    For i = 2 To lRow
        If .Cells(i, "G").Value = Test Then
            ' date in G is Yesterday
            .Cells(i, "A").Value = Trim(.Cells(i, "A").Value)
        Else
            ' date in G is NOT Yesterday
        End If
    Next i
End With

End Sub

Turning off screenupdating will make your original macro run faster since the slowest part of your macro is the Excel UI and not the code.

Sub TrimText()
Dim lRow As Integer

Application.ScreenUpdating = False

With Worksheets("Data")
    lRow = .Range("A2").End(xlDown).Row
    
    For i = 2 To lRow
        .Cells(i, "A").Value = Trim(.Cells(i, "A").Value)
    Next i
End With

Application.ScreenUpdating = True

End Sub