Code to Merge across numerous columns

[Miche25]

I have the following code, however I need "Customer Name" to merge and centre across columns B to J instead of appearing in just column B. How do I change the below code to reflect this?

Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 3).Select
ActiveCell.FormulaR1C1 = "Customer Name"
Selection.Font.Bold = False
With Selection.Font
.Name = "Arial"
.Size = 18
End With

 

[Joe4]

I would highly recommend using the "Center Across Selection" format option instead of merging the cells. It gives the same visual effect that merging across a row does, but without all the issues that merged cells cause (merged cells are very problematic for things like sorting and VBA, and should really be avoided whenever possible).

See: http://blog.contextures.com/archives/2014/04/10/center-headings-without-merging-cells/

If you use the Macro Recorder, you can get the code for that, i.e.

    Range("B4:J4").HorizontalAlignment = xlCenterAcrossSelection

 

[Miche25]

Thanks, the row can differ and is not static. how would I change your code to make it dynamic instead of row 4?

 

[Joe4]

how would I change your code to make it dynamic instead of row 4?

It depends on your answer to the following question:
What is the logic for dynamically determine which row to apply this to?
In another words, how do we locate this row you want to do this to?