We have created a macro that will enable us to move text within a cell over two spaces. It works perfectly but only for the first line of text. If we have two lines of text in one cell, thus having used Alt+enter to type a second line in a cell, this macro doesn't move the second line of text over the two spaces. Therefore the macro moves the first line over the two spaces but leaves the second line to the left of the cell. Can anyone help me with figuring out what I can add to this macro to move the second line over as well. My macro is posted below. Thank you.
Sub Space()
Dim cell As Range, rngCol As Range
' Space Macro
' To add Space
'
' Keyboard Shortcut: Ctrl+s
Application.ScreenUpdating = False
On Error Resume Next
Set rngCol = ActiveCell.EntireColumn.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rngCol Is Nothing Then
For Each cell In rngCol
If InStr(1, cell.Value, " ") = 0 Then 'exclude cells already with double spaces
If IsNumeric(cell.Value) Then
cell.Value = "' " & cell.Value 'for numbers
Else
cell.Value = " " & cell.Value 'for text
End If
End If
Next cell
End If
Application.ScreenUpdating = True
End Sub
Bookmarks