behest
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!
Thank you!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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?
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
Example.xlsm
Looking at bigger picture, why do you need a space on the end of cell contents??
Thanks
Rob H
Thanks
Rob H
> Looking at bigger picture, why do you need a space on the end of cell contents??
+1
+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:
hth
Rob
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
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.