Trim cell values in rows and columns in different sheets VBA

Question

I would like to trim the cells that have spaces in them. But the macro I now have does not work.

Sub DoSomething()

    'Declare Variables
    Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long
    Dim shtData, shtInput, shtInputI, shtInputII, shtInputIII, shtInputIV, shtInputV, shtInputVI, shtCopy As Worksheet
    Dim Cell As Range
    Set shtData = Sheets("PQFR")
    Set shtInput = Sheets("INPUT")
    Set shtInputI = Sheets("INPUTI")
    Set shtInputII = Sheets("INPUTII")
    Set shtInputIII = Sheets("INPUTIII")
    Set shtInputIV = Sheets("INPUTIV")
    Set shtInputV = Sheets("INPUTV")
    Set shtInputVI = Sheets("INPUTVI")
    Set shtCopy = Sheets("INPUTR")
    lastColData = shtData.Cells(4, shtData.Columns.count).End(xlToLeft).Column
    LastRowData = shtData.Cells(shtData.Rows.count, "A").End(xlUp).row
    lastRowInput = shtInputI.Cells(shtInputI.Rows.count, "A").End(xlUp).row
    'lastRowData = 10


    'Remove spaces from these sheets:
    shtInput.Cells(r, 32).Value = Trim(shtInput.Cell(r, 32).Value)
    shtInputI.Cells(4, c).Value = Trim(shtInputI.Cells(4, c)).Value
    shtInputII.Cells(4, c).Value = Trim(shtInputII.Cell(4, c)).Value
    shtInputIII.Cells(4, c).Value = Trim(shtInputIII.Cell(4, c)).Value
    shtInputIV.Cells(4, c).Value = Trim(shtInputIV.Cells(4, c)).Value

End Sub

Please not that *"does not work"* is no valid error description. Tell which error you get and where. Also note that `Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long` only declares the last variable as `Long` but all the others as `Variant` you need to specify a type for **every** variable: `Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long` — Pᴇʜ, Nov 21 '18 at 10:34
Also note that `Trim(shtInputI.Cells(4, c)).Value` cannot work because `Trim` has no `.Value` the parenthesis is set in the wrong place. — Pᴇʜ, Nov 21 '18 at 10:36
The error I get is a 1004 Error during performance, due to the appliance or wrong defined object.This is shown as a yellow mark on the first Trim function. — Daphn123, Nov 21 '18 at 10:40
of course because you did not define a value for `r` it is `r = 0` and row `0` does not exist. Also you should fix the issues I mentioned above and [edit] your code in the question. — Pᴇʜ, Nov 21 '18 at 10:56
There's no value for c either so the same reason as @Pᴇʜ mentions — Tom, Nov 21 '18 at 11:54

score 0 · Answer 1 · edited Nov 21 '18 at 12:52

It's because you didn't make the loop. If you explain me better what you want, I can let the way you want.

Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long


For Each Worksheet In ActiveWorkbook.Worksheets
    If Worksheet.Name = "PQFR" Or Worksheet.Name = "INPUT" Then 'please do for the sheets you want your command gonna`s be executed
        Sheets(Worksheet.Name).Select
        lastColData = Cells(4,Columns.count).End(xlToLeft).Column
        LastRowData = Cells(Rows.count, 1).End(xlUp).Row
        lastRowInput = Cells(Rows.count, 1).End(xlUp).Row

        'using c=32 just for input
        If Worksheet.Name = "INPUT" Then
            'using r = 4 fosheets
            For c = 32 To lastColData
                For r = 1 To LastRowData
                    Cells(r, c) = Trim(Cells(r, c).Value)
                Next r
            Next c
        Else
            'using r = 4 for all the other sheets
            For c = 1 To lastColData
                For r = 4 To LastRowData
                    Cells(r, c) = Trim(Cells(r, c).Value)
                Next r
            Next c
        End If
    End If
Next Worksheet

Note that `Dim i, r, c, count, lastColData, LastRowData, lastRowInput, StartSearch As Long` only declares the last variable as `Long` but all the others as `Variant` you need to specify a type for every variable: `Dim i As Long, r As Long, c As Long, count As Long, lastColData As Long, LastRowData As Long, lastRowInput As Long, StartSearch As Long` — Pᴇʜ, Nov 21 '18 at 12:51

Trim cell values in rows and columns in different sheets VBA

1 Answers1