Set column to bold, then combine columns

[FredrikNilsen]

I have a code to combine data from two columns: E and F.

Sub MoveData()

Columns("E").Font.Bold = True

Application.ScreenUpdating = False
For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    Range("A" & i, "E" & i).VerticalAlignment = xlVAlignTop
    Range("E" & i) = Range("E" & i) & vbLf & Range("F" & i).Value
    Range("F" & i).ClearContents
Next
Application.ScreenUpdating = True

End Sub

I have added the "Columns("E").Font.Bold = True" part to make the current data in E bold, but when I run it, it also makes the new data from F bold, which it should not. Any suggestions?

 

[bebo021999]

Column E always "bold" with that statement
Try .characters method to set bold to string in E only
it should set for "new" E, start from 1 to "old" E lenght

Sub MoveData()
Application.ScreenUpdating = False
Lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = Lr To 1 Step -1
    Range("E" & i) = Range("E" & i) & vbLf & Range("F" & i).Value
    Range("E" & i).Characters(1, Len(Range("E" & i)) - Len(Range("F" & i))).Font.Bold = True
Next
Application.ScreenUpdating = True
Range("A1:E" & Lr).VerticalAlignment = xlVAlignTop
Range("F1:F" & Lr).ClearContents
End Sub

 

[Peter_SSs]

A slightly different approach. This does all the text combining in a single go (as well as the alignment and clearing like the previous code) and also provides a simpler way to calculate the number of characters that need to be bolded using the inserted vbLf character.

BTW, I also highly recommend always declaring all variables and forcing your self to do so by making this setting in your vba window.

Sub MoveData_v2()
  Dim c As Range
  
  Application.ScreenUpdating = False
  With Range("E1:E" & Cells(Rows.Count, "A").End(xlUp).Row)
    .Value = Evaluate(.Address & "&char(10)&" & .Offset(, 1).Address)
    .EntireRow.Resize(, 5).VerticalAlignment = xlVAlignTop
    .Offset(, 1).ClearContents
    For Each c In .Cells
      c.Characters(1, InStr(1, c.Value, vbLf)).Font.Bold = True
    Next c
  End With
  Application.ScreenUpdating = True
End Sub

 

[FredrikNilsen]

A slightly different approach. This does all the text combining in a single go (as well as the alignment and clearing like the previous code) and also provides a simpler way to calculate the number of characters that need to be bolded using the inserted vbLf character.

BTW, I also highly recommend always declaring all variables and forcing your self to do so by making this setting in your vba window.

View attachment 52293

Sub MoveData_v2()
  Dim c As Range
 
  Application.ScreenUpdating = False
  With Range("E1:E" & Cells(Rows.Count, "A").End(xlUp).Row)
    .Value = Evaluate(.Address & "&char(10)&" & .Offset(, 1).Address)
    .EntireRow.Resize(, 5).VerticalAlignment = xlVAlignTop
    .Offset(, 1).ClearContents
    For Each c In .Cells
      c.Characters(1, InStr(1, c.Value, vbLf)).Font.Bold = True
    Next c
  End With
  Application.ScreenUpdating = True
End Sub

Works like a charm, thank you! And thank you for good advice, I'm learning.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.