Results 1 to 5 of 5

Thread: Macro code for Inserting space in Cells

  1. #1

    Post Macro code for Inserting space in Cells



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 ! !

  2. #2

    Post 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)

  3. #3
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    Why not attach a sample workbook?, the data can be dummy data but must be of the same style and structure.
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  4. #4

    Post Macro code for Inserting space in Cells

    Hi,
    Simson here is the attachment of sample sheet.
    Attached Files Attached Files

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Uh... the input and output look the same to me in the example...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •