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

 

[Ruddles]

Alt-Enter isn't a space, it's a line feed character.

Try this:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Public Sub InsertLF(ByRef oCell As Range)[/FONT]
 
[FONT=Fixedsys]  Dim iPtr As Long[/FONT]
[FONT=Fixedsys]  Dim sTemp As String[/FONT]
[FONT=Fixedsys]  Dim iLF As Long[/FONT]
 
[FONT=Fixedsys]  sTemp = oCell[/FONT]
 
[FONT=Fixedsys]  iPtr = InStrRev(sTemp, "(Active)")[/FONT]
[FONT=Fixedsys]  Do Until iPtr = 0[/FONT]
[FONT=Fixedsys]    sTemp = Left(sTemp, iPtr)[/FONT]
[FONT=Fixedsys]    iLF = InStrRev(sTemp, vbLf)[/FONT]
[FONT=Fixedsys]    If iLF > 0 Then[/FONT]
[FONT=Fixedsys]      sTemp = Left(sTemp, iLF - 1)[/FONT]
[FONT=Fixedsys]      iLF = InStrRev(sTemp, vbLf)[/FONT]
[FONT=Fixedsys]      If iLF > 0 Then[/FONT]
[FONT=Fixedsys]        oCell = Left(oCell, iLF) & vbLf & Mid(oCell, iLF + 1)[/FONT]
[FONT=Fixedsys]      End If[/FONT]
[FONT=Fixedsys]    End If[/FONT]
[FONT=Fixedsys]    iPtr = InStrRev(sTemp, "(Active)")[/FONT]
[FONT=Fixedsys]  Loop[/FONT]
[FONT=Fixedsys]  If Left(oCell, 1) <> vbLf Then oCell = vbLf & oCell[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

Call the code like this:-

[FONT=Fixedsys][FONT=Fixedsys]InsertLF Range("A1")[/FONT]
[FONT=Fixedsys]Call InsertLF(Range("X99"))[/FONT]
InsertLF Cells(5,5)
[/FONT]

or:-

[FONT=Fixedsys]InsertLF ActiveCell[/FONT]
[FONT=Fixedsys]Call InsertLF(ActiveCell)[/FONT]

 

[sanz]

Thnx for your reply, that code worked . .

Also want to kno how can it use this code for range of cells
ie."A1:A101"

 

[Peter_SSs]

Does this work for you? Test in a copy of your workbbok. It asumes the data is in column A.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Add_Blanks()<br>    <SPAN style="color:#00007F">Dim</SPAN> a<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, z <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    z = Chr(10)<br>    <SPAN style="color:#00007F">With</SPAN> Range("A1", Range("A" & Rows.Count).End(xlUp))<br>        a = .Value<br>        <SPAN style="color:#00007F">With</SPAN> CreateObject("VBScript.RegExp")<br>            .Global = <SPAN style="color:#00007F">True</SPAN><br>            .Pattern = "(" & z & ")(?=.*" & z & "\(Active\))"<br>            <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(a, 1)<br>                s = z & a(i, 1)<br>                a(i, 1) = Replace(.Replace(s, "$1$1"), z, "", 1, 1)<br>            <SPAN style="color:#00007F">Next</SPAN> i<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        .Value = a<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Ruddles]

Also want to kno how can it use this code for range of cells ie."A1:A101"

I see Peter has also replied. Please try his code also.

My code is written as a suboutine which processes just the single cell passed to it as a parameter. To apply it across a range of cells you would have to loop round each cell you wanted to change:-

dim ocell as range
for each ocell in range("a1:a101")
  insertlf ocell
next ocell

or:-

dim irow as long
for irow=1 to 101
  insertlf cells(irow,"a")
next irow

or:-

dim irow as long
for irow=1 to 101
  insertlf range("a" & irow)
next irow

 

[sanz]

Hi,
Ruddles thanks for your reply,
Can u plz add the loop in the code u had given, m not able to find where it should b substituted / added. (Newbie for code)

 

[sanz]

Thnx Peter_SSs
your code worked to.

 

[Ruddles]

If Peter's code works better than mine, use that.

My code is a subroutine for processing a single cell. If you wanted it to process a number of cells you would have to call it repeatedly. How you do that is up to you as it depends on which cells you want to process, but you could for example do something like this:-

[FONT=Fixedsys]sub changecolumna()[/FONT]
[FONT=Fixedsys]  for irow=1 to cells(rows.count,"a").end(xlup).row[/FONT]
[FONT=Fixedsys]    insertlf cells(irow,"a")[/FONT]
[FONT=Fixedsys]  next irow[/FONT]
[FONT=Fixedsys]end sub[/FONT]