Macro code for Inserting space in Cells

sanz

New member
Joined
Jul 19, 2011
Messages
3
Reaction score
0
Points
0
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 ! !
 
Macro for inserting feed character in excel

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)
 
Why not attach a sample workbook?, the data can be dummy data but must be of the same style and structure.
 
Hi,
Simson here is the attachment of sample sheet.
 

Attachments

  • Sample sheet.xlsx
    9.6 KB · Views: 23
Back
Top