In VBA, there is a “MERGE” method that you can use to merge a range of cells or even multiple ranges into one. This method has an argument “Across” which is optional. If you specify TRUE it will merge each row in the range separately, and if you specify FALSE it will merge the entire range as one.
Steps to follow to use VBA to Merge Cells:
- First, you need to define the range of cells that you want to merge.
- After that, type a (.) dot to get the list of properties and methods and select “Merge” from that list or you can type it directly.
- After that, you need to define the argument “Across” as TRUE or FALSE.
- In the end, run that code to merge the range.
Here’s the code.
And if you want to merge rows across the range you can use TRUE for the argument across.
In the above code, you have the selection as the range, the merge method, and across as true. And it has merged all the rows in the selected range.
Now, let’s say you want to merge the range of cells from another worksheet, you don’t need to activate it first. You can simply refer to the worksheet first and then use the merge methods with the renege that you want to merge.
In the same way, you can refer to a workbook as well by specifying the name of the workbook.
Merge a Range and Align the Cell Content
Now when you merge two or more cells you need to align the content that you have in the cell. Look at the below snapshot where you have cell A1 and A2 merged and the value in the cells is aligned bottom.
To deal with this problem, you can use the alignment property and align the cell content to the center and middle so that it’ll be easy for you to read it.
With Range("A1:A2") .Merge .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With
Combine the Text and then Merge
Let talk about a real-life scenario. You have text in a range of cells and you to merge those cells but don’t want to lose the content that you have.
Well, you can write a code loop through the entire range using for each and combine text and then add it to the merged range in the end.
- In the first part, you have variables to use to store values from the range and define itself.
- In the second part, you have a LOOP to combine value from each cell to create a new string with that.
- In the third part, merge the range, add the combined string, apply the wrap, and alignment of the cell content.
Here’s the full code.
Sub vba_merge_with_values() Dim val As String Dim rng As Range Set rng = Range("A1:A10") For Each Cell In rng val = val & " " & Cell.Value Next Cell With rng .Merge .Value = Trim(val) .WrapText = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With End Sub
More on VBA Range and Cells
- How to Set (Get and Change) Cell Value using a VBA Code
- How to Sort a Range using VBA in Excel
- How to Create a Named Range using VBA (Static + Dynamic) in Excel
- How to Check IF a Cell is Empty using VBA in Excel
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- Excel VBA Font (Color, Size, Type, and Bold)
- How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
- How to use OFFSET Property with the Range Object or a Cell in VBA
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- How to Copy a Cell\Range to Another Sheet using VBA
- How to use Range/Cell as a Variable in VBA in Excel
- How to Find Last Rows, Column, and Cell using VBA in Excel
- How to use ActiveCell in VBA in Excel
- How to use Special Cell Method in VBA in Excel
- How to Apply Borders on a Cell using VBA in Excel
- How to Refer to the UsedRange using VBA in Excel
- How to Change Row Height/Column Width using VBA in Excel
- How to Select All the Cells in a Worksheet using a VBA Code
- How to Insert a Row using VBA in Excel
- How to Insert a Column using VBA in Excel