Macro to TRIM columns in different sheets with different start rows

[agrospud]

Hello,

I am working on a macro to work with two sheets, with one sheet holding a source table for data to update a monthly pricelist in the destination sheet. The macro will add a description category from the source sheet to the destination sheet using a vba vlookup and then rearrange the layout of the pricelist the way I require it to look.

I noticed that the list of item codes for each item in the pricelist has a heap of trailing spaces after each item in the code list. I don’t know if this will vary as new lists are provided to me so I need to use TRIM to tidy up both the source item code and the reference code that the vlookup uses. Both lists of item codes are in column A of the two sheets but start in different row numbers. This caused me a bit of difficulty in trying to run the same TRIM code in two separate worksheets that start in different row numbers. I have been working on the components of the macro to get them working and then will combine them into one macro.

I have actually managed to get the TRIM part of the macro to work after much trial and error but I am not convinced I have done it the best way so am offering it up for advice on either improving it or alternatives please. The separate vlookup and layout components work well so are not shown.

Thanks.

Sub Looping()

Dim ws As Worksheet
Dim Cel As Range

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
If ws.Name = "SourceData" Then
For Each Cel In Range("A2", Range("A" & Rows.Count).End(xlUp))
Cel.Value = Trim(Cel.Value)
Next Cel
End If

If ws.Name = "Price List - Email" Then
For Each Cel In Range("A7", Range("A" & Rows.Count).End(xlUp))
Cel.Value = Trim(Cel.Value)
Next Cel
End If
Next

End Sub

 

[Alex Blakenburg]

This should be faster:

Sub LoopingMod()

    Dim ws As Worksheet
    Dim rng As Range
    Dim arr As Variant
    Dim i As Long
    
    Application.ScreenUpdating = False
   
    Set ws = Worksheets("SourceData")
    Set rng = ws.Range("A2:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
    arr = rng
    For i = 1 To UBound(arr)
        arr(i, 1) = Trim(arr(i, 1))
    Next i
    rng = arr
    
    Set ws = Worksheets("Price List - Email")
    Set rng = ws.Range("A7:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
    arr = rng
    For i = 1 To UBound(arr)
        arr(i, 1) = Trim(arr(i, 1))
    Next i
    rng = arr
    
    Application.ScreenUpdating = True

End Sub

PS: You might want to update your profile to show what version of Excel you are using.

 

[agrospud]

Hi Alex,
Works beautifully, thank you very much!
It has the added touch of the starting sheet staying as the active sheet, unlike my code which changed to the other one and it became the active one.
I have not used the UBound function before so will have to get my head around it.
Agrospud

 

[Alex Blakenburg]

When you load a range into a variable that is just declared as a variant, it creates an array on the fly.
UBound returns what the max item number is in the array, so your For loop has an an end point for the item number counter.
(Not sure I’ve explained that very well but let me know how you go)

Anyway thanks for the feedback and glad I could help.