Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • Last Modified:

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!
0
behest
Asked:
behest
1 Solution
 
Michael FowlerSolutions ConsultantCommented:
Add this macro to your workbook and run it once

Sub AddSpace()
    Dim i As Long
    For i = 1 To Range("D" & Rows.Count).End(xlUp).Row
        Range("D" & i).Value = Range("D" & i).Value & " "
    Next
End Sub

Open in new window

0
 
bbaoIT ConsultantCommented:
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?
0
 
Michael FowlerSolutions ConsultantCommented:
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
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
behestAuthor Commented:
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?
0
 
Michael FowlerSolutions ConsultantCommented:
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
0
 
Rob HensonIT & Database AssistantCommented:
Looking at bigger picture, why do you need a space on the end of cell contents??

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

+1
0
 
broro183Commented:
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
0
 
bbaoIT ConsultantCommented:
better wait for the author's feedback about his 'big picture' to determine the best solution.
0
 
Martin LissRetired ProgrammerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now