VBA Merge Range cells in Excel explained with Examples

VBA Merge range or cells in Excel will merge multiple cells in a excel worksheet using merge method of range object and then creates a merged cell. ‘Range.Merge’ method will merge the multiple cells or range, and then after merging it will make it as one singular cell at upper left corner of the range .

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


VBA Merge Method Excel Range Object

VBA Merge Range – Syntax

Here is the syntax to merge range or multiple cells in a worksheet. You can merge the multiple cells including the formats using ‘Range.Merge’ method.

Range(“YourRange”).Merge(

[Across])

Where
Across: It is Optional. When we mention Across as ‘True’ that means it will merge cells in each row of the specified range as separate merged cells. The default value is False.

VBA Merge Range – Example1

Here is the example to merge multiple cells using ‘Range.Merge’ method of range object in worksheet when across is false.

Sub Range_Merge_Across_False()
Range("A1:D10").Merge
End Sub

The above macro or code will show you how to merge range(“A1:D10”) or multiple cells using VBA. In this example, we are merging range “A1 to D10” using ‘Range.Merge’ method of range object. Now the range(“A1:D10”) result will be a single cell named ‘A1’. In the above example across value is False. Please find the below screenshot for the same after merging the cells which is highlighted in yellow color.

Excel VBA Range Merge_False

VBA Merge Range – Example2

Here is the example to merge multiple cells using ‘Range.Merge’ method of range object in worksheet when across is true.

Sub Range_Merge_Across_True()
Range("A1:D10").Merge(True)
End Sub

The above macro or code will show you how to merge range(“A1:D10”) or multiple cells using VBA. In this example, we are merging range “A1 to D10” using ‘Range.Merge’ method of range object. Now the range(“A1:D10”) will be ten cells from ‘A1:A10’ instead of ‘A1:D10’. In the above example across value is True.Please find the below screenshot for the same after merging the cells which is highlighted in yellow color.

Excel VBA Range Merge_True

VBA Merge Rows- Example Macro

Here is the example vba code to merge multiple rows using ‘Range.Merge’ method of range object in worksheet.

Sub sbMergeRows()
Range("5:10").Merge
End Sub

This code will merge entire rows from row 5 to row 10.

VBA Merge Column – Example Macro

Here is the example vba code to merge multiple coluns using ‘Range.Merge’ method of range object in worksheet.

Sub sbMergeColumns()
Range("B:E").Merge
End Sub

This code will merge entire columns from column B to Column E.

Please note: Do not test sbMergeColumns code with the above example (sbMergeRows). As some of the cells are common in both and it will merge the maximum possible range. Test this examples on individual worksheets to see the output.

LIMITED TIME OFFER

3 Comments

  1. Younis January 20, 2016 at 12:53 PM - Reply

    Sir if we want to merge columns, like that A1:B1 and C1:D1 and E1:F1 and G1:H1.

    • PNRao January 21, 2016 at 9:25 PM - Reply

      Hi Younis,
      I have updated the article, please check the last two examples.
      Thanks-PNRao!

  2. di April 20, 2016 at 11:46 AM - Reply

    Hi I want to merge the rows column wise. Please suggest

Leave A Comment


Related pages


how to use the countif function in excel 2013unhide column a in excel 2010averageif functionexcel vba data typesvba vlookup codevba word application objecthow to colour a cell in excelexcel delete duplicate entriesexcel vba import text fileunlock a excel spreadsheetfree construction estimating spreadsheet templateexcel button in cellsumif function excel 2007excel vba column rangeexcel how to hide columnskpi dashboard examples excelhow do you remove password protection from excel 2010macro in excel exampleexcel vba file pathtotal no of rows and columns in excelexcel 2010 match functionfinding duplicates excelexcel vba language referencethe vlookup functionunprotecting excelhow to remove password from protected excel sheetexcel getopenfilenameworkbook methodshow to hide tabs in excelexcel formula explanationsexcel macro enabled workbookunprotect excel 2010excel auto adjust row heighthow to run macros in excelexcel macro to change cell colorhow to do a vlookup in excel 2013sample excel macro codecopy and paste cells in excelexcel dashboard widgets freevba carriage returnmastering excel 2010ranges in vbadata modeling interview questions and answers pdfhow do you insert a column in excelnested if functions in excel 2010what is a vlookup used forhow to eliminate duplicates in excelvba excel while loopexcel programming commandsvba userform examplehow to get developer tab in excelcool excel vba codevlookup using two criteriasql pivot table tutorialname manager excel 2010excel consolidate columnsarray in vba excelduplicates in excelsample tosexcel macro examplesvba useshow to check for duplicates excelqlikview beginners guideexcel vba sample codetextbox in vbahow to use vlookup excel 2013isblank function excelbasics of sql commandsexcel count duplicatespastespecialhighlight duplicatesvlookup in excel vba with examplesexcel chart wizardexcel unlock cells