Macro code for Inserting space in Cells

[sanz]

Hi,
I want a macro code for adding space above two line in a cell.
Eg:-

"ABC
(Active)
XYZ
ADS
(Active)"

I want to add a space(using alt+enter) 2line above (Active).....
ie./output should be
"
ABC
(Active)
XYZ

ADS
(Active)"

Any help would be greatly appreciated ! !

[sanz]

Want to use this below code for range of column
ie "A1:A50"

Code:
Option Explicit

Public Sub InsertLF(ByRef oCell As Range)

Dim iPtr As Long
Dim sTemp As String
Dim iLF As Long

sTemp = oCell

iPtr = InStrRev(sTemp, "(Active)")
Do Until iPtr = 0
sTemp = Left(sTemp, iPtr)
iLF = InStrRev(sTemp, vbLf)
If iLF > 0 Then
sTemp = Left(sTemp, iLF - 1)
iLF = InStrRev(sTemp, vbLf)
If iLF > 0 Then
oCell = Left(oCell, iLF) & vbLf & Mid(oCell, iLF + 1)
End If
End If
iPtr = InStrRev(sTemp, "(Active)")
Loop
If Left(oCell, 1) <> vbLf Then oCell = vbLf & oCell

End Sub
Call the code like this:-
Code:
InsertLF Range("A1")
Call InsertLF(Range("X99"))
InsertLF Cells(5,5)

or:-
Code:
InsertLF ActiveCell
Call InsertLF(ActiveCell)

[Simon Lloyd]

Why not attach a sample workbook?, the data can be dummy data but must be of the same style and structure.

[sanz]

Hi,
Simson here is the attachment of sample sheet.

[Ken Puls]

Uh... the input and output look the same to me in the example...