CODEWITHSUNDEEP
excelmacrosvba
Siraj
Siraj

Reputation: 195

Add line breaks in cell to 1000 rows of data

I am currently using the below code to add a line break to cell data in column C and copy it to column K. I need to apply line breaks to a range of data. I have 1000 rows of data in column C. Any help will be much appreciated.

Sub Macro
    Dim Stem As Variant
    Stem = ThisWorkbook.Worksheets ("Sheet1").Range("C2")
    Range ("K2").Select
    Range("K2").FormulaR1C1 = Stem & Chr(10) & ""
End Sub

Thanks

Upvotes: 0

Views: 705

Answers (3)

SandPiper
SandPiper

Reputation: 2906

A couple of things:

  • Better to early bind your variables than late (better memory management, take advantage of intellisense, etc.)
  • Usually best practice to avoid using "select" if possible.
  • Your Stem variable is an object (Range Object) and thus needs to be "Set"

Try this:

Sub Macro
    Dim WS As Worksheet
    Dim Stem As Range
    Dim R2 As Range
    Dim Rng as Range

    Set WS = ActiveWorkbook.Sheets("Sheet1")
    Set Stem = WS.Range("C2", Cells(WS.Range("C2").End(xlDown).Row, WS.Range("C2").Column))
    Set R2 = WS.Range("K2", Cells(Stem.End(xlDown).Row, WS.Range("K2").Column))

    R2.Value = Stem.Value
    'New Code
    For Each Rng In R2
        Rng.Value = Rng.Value & Chr(10) & ""
    Next Rng
    'Old Code: R2.End(xlDown) = R2.End(xlDown) & Chr(10) & ""

End Sub

What this does is first sets the worksheet you're using. Then, you set your working range (Stem) using the Range(cell1, cell2) format. Cell1 I defined as "C2". The next expression there is using the Cells() function. It is the VBA equivalent of being in "C2" and hitting Ctl+Down, then seeing what row you're in.

Then, I set your destination range, R2, in a similar manner, but I used the Stem range to determine how large it should be.

Finally, to get an accurate copy your destination range must be the same size as your from range. The .value to .value expression pastes the data. Then, your extra characters are added on to your new data field.

Something to keep in mind with .End(xlDown)... if you have blank rows in the middle of your data it will stop there, not go all the way down to the end. Hope this helps!

EDIT: The For Each loop will go through every range (i.e. cell) in your destination range, R2, and add your new characters. Hope that fits better for you.

Upvotes: 1

Siraj
Siraj

Reputation: 195

Thanks to all for your answers. I atlas was able to write my first script and got the code to add line break inside the cell.

Sub AddLineBreak()
Dim Stem As Variant
Stem = ThisWorkbook.Worksheets("Sheet1").Range("C2")

Dim i As Integer
i = 2
'this will terminate the loop if an empty cell occurs in the middle of data
Do While Cells(i, "C").Value <> ""
  Cells(i, "K").Value = Cells(i, "C").Value & vbCrLf
  i = i + 1
Loop

End Sub

Upvotes: 0

Pav
Pav

Reputation: 276

Try this:

Sub copyAndNewLine()
'copy column C to K
Columns("C").Copy Destination:=Columns("K")

'loop through all cells in K and add new line
For i = 2 To Cells(Rows.Count, "K").End(xlUp).Row
    Cells(i, "K").Value = Cells(i, "K").Value & vbCrLf
Next i
End Sub

Upvotes: 1

Related Questions