Link to home
Start Free TrialLog in
Avatar of behest
behestFlag for United States of America

asked on

add space to end of cell content

Hi! I'm looking for a quick and easy way to add spaces to the end of the content of every cell in column D of a worksheet. Cell format is general and I just want to go to the end of the content and add a single space.

Thank you!
ASKER CERTIFIED SOLUTION
Avatar of Michael Fowler
Michael Fowler
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bbao
or add one more column with formular =D1&" " and populate it to the following rows, then hide column D and only show the added column?
Instead of hiding column D for @bbao's solution just copy the the column with the formula above and then paste special> values into column D and delete the formula column
Avatar of behest

ASKER

Set up a separate macro as you defined. When I attempt to run it, I get the following error:

Run-time error '1004':

Application-defined or object-defined error.

Suggestions?
Not sure what has happened, I have tested and it works fine. Here is a sample file so you can see it in action
Example.xlsm
Looking at bigger picture, why do you need a space on the end of cell contents??

Thanks
Rob H
> Looking at bigger picture, why do you need a space on the end of cell contents??

+1
I agree with Rob H too, give us more context & this intermediate step may be able to be removed once we understand your complete requirement.

In saying that, here is a slight alternative to Michael's version:
Option Explicit

Sub AddSpaceToRng()
'10/07/2011, RB: written as "Sub TrimRng()" - see commented out line within the loops
Const Space_Chr As String = " "
Dim Rng As Range
Dim rngArr As Variant
Dim NumOfRows As Long
Dim NumOfCols As Long
Dim RowInd As Long    'Row Index for looping
Dim ColInd As Long    'Column Index for looping

    With ActiveSheet
        'Set Rng = Selection
        Set Rng = .Range("d1:d" & .Range("D" & .Rows.Count).End(xlUp).Row)
    End With

    With Rng
        'rngArr = .Cells
        rngArr = .Cells.Value2
        NumOfRows = .Rows.Count
        NumOfCols = .Columns.Count
    End With
    For RowInd = 1 To NumOfRows
        For ColInd = 1 To NumOfCols
            'rngArr(RowInd, ColInd) = Trim$(rngArr(RowInd, ColInd))
            rngArr(RowInd, ColInd) = rngArr(RowInd, ColInd) & Space_Chr
        Next ColInd
    Next RowInd
    'Rng = rngArr
    Rng.Value2 = rngArr
    MsgBox "done"
    Set Rng = Nothing
End Sub

Open in new window


hth
Rob
better wait for the author's feedback about his 'big picture' to determine the best solution.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.