Merge Data From Two Cells In Excel

• Merge Data From Two Cells In Excel Without
• How Do You Combine Data From Two Cells In Excel

Users of UW-Madison's institutional Tableau workbooks may need to pull data from one Microsoft Excel spreadsheet into another spreadsheet. This KB article explains how, by using an Excel formula called vLookup.

Combine data from two different cells, first and last name for example, by using a simple Excel formula. There are a couple of quick ways you can combine all data from two columns without losing anything. Merge Columns Using Notepad. One easy way to combine data from the two columns into one is copying all of the data from the two columns into notepad. Notepad's search and replace feature is an effective way to quickly format the two pieces of. In Excel, you can combine or merge text from two or more cells, as well as columns and rows,into one cell. If you have a lot of rows of data where you want to combine text, you can simply start typing the combined text in an adjacent column and Excel will fill in the rest for you.

1. Locate where you want the data to go. Click that cell only once.
   
   
2. At the top, go to the Formulas tab and click Lookup & Reference.
   
   
3. Select vLookup
   
   
4. Excel's vLookup wizard will pop up. We'll walk through each part of the formula.
   
   
   
5. Lookup_value
   Find the Unique Identifier (lookup value). It is usually in the same row as the empty cell you selected.
   Click once on the Unique Identifier so that the cell position will automatically fill in. In this example it is cell B2.
   
   
   
6. Go to the next field, Table_array (click in it once). In Spreadsheet 2 highlight the table containing the info you want, starting with the Unique ID.
   
   
   In this example, Excel looks up Campus ID 555123123 in the first highlighted column of Spreadsheet 2.
   Note: Make sure each Unique ID is listed only once in the table_array (on the second spreadsheet) so that vLookup retrieves the correct value. For example, if 555123123 is duplicated in the table_array, where Student 1@wisc.edu is the email in one row and Student abc@wisc.edu in the other, Excel will choose one of the emails for you.
   
7. Go to Col_index_num (click in it once). This identifies which column contains the information you want from Spreadsheet 2.
   Type the number of columns your field is from the Unique ID, where the Unique ID is 1. Here, the Email field is the third column.
   
8. Go to Range_lookup (click in it once). Type FALSE to search for exact matches. The result will look something like this:
   
   
   
9. Finally, copy and paste the formula to pull emails for the rest of the column.
   (Note: if your table array is in the same Excel workbook, put $ signs around the cell values, similar to the example below. This ensures that you reference the correct cells in the table array, meaning that the table array does not shift down when you paste the formula down. See Advanced Tip below for more details.)
   

1. Type the beginning of the formula: =VLOOKUP(
   The formula guide will appear below.
   (Note: You may notice Excel displays the formula in 2 places: the formula bar above and directly in the cell. You can edit the formula in either place.)
   
   
   
2. Follow the guide and enter each value. Remember to insert a comma between each value.
   
3. Insert a closed parenthesis ) and hit Enter. The end result will look like something like this:
   =VLOOKUP(B2,'[Spreadsheet Name.xlsx]SheetName'!$B$1:$E$11,3,FALSE)
   
4. Finally, copy and paste the formula to pull emails for the rest of the column. Keep relative references in mind and use $ signs where necessary. (See Advanced Tip below for more details.)

• To lock in the lookup value in cell B1, insert $ signs before the column and the row:
  =VLOOKUP($B$1,'[Spreadsheet2.xlsx]SheetName'!$B$1:$E:$11,3,FALSE)
• To lock in the column only, insert a $ before B only.
• To lock in the row only, insert a $ before 1 only.
  

Need More Information or Help?

Merge Data From Two Cells In Excel Without

In this VBA Tutorial, you learn how to merge cells and unmerge cells in a variety of ways.

This VBA Tutorial is accompanied by Excel workbooks containing the data and macros I use in the examples below. You can get immediate free access to these example workbooks by subscribing to the Power Spreadsheets Newsletter.

Use the following Table of Contents to navigate to the section you're interested in.

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

• General VBA constructs and structures:
  
  
  • Learn about using variables here.
    
    
  • Learn about VBA data types here.
    
    
  • Learn about R1C1 and A1 style references here.
    
    
• Practical VBA applications and macro examples:
  
  
  • Learn how to work with worksheets here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Merge Cells

VBA Code to Merge Cells

To merge cells with VBA, use a statement with the following structure:

Process Followed by VBA Code

VBA Statement Explanation

1. Item: Worksheet.
   
   
   • VBA Construct: Workbook.Worksheets property.
     
     
   • Description: Returns a Worksheet object representing the worksheet you work with.
     
     
2. Item: Range('FirstCell:LastCell').
   
   
   • VBA Construct: Worksheet.Range property.
     
     
   • Description: Returns a Range object representing the cell range between FirstCell and LastCell. This is the cell range you merge.
     
     Specify FirstCell and LastCell using an A1-style cell reference. Separate FirstCell and LastCell using the range operator, a colon (:). Enclose the entire cell range address within quotations ('').
     
     
3. Item: Merge.
   
   
   • VBA Construct: Range.Merge method.
     
     
   • Description: Merges the cells represented by the Range object you specify in item #2 above to create a merged cell.

Macro Example

The following macro merges cells A5 to E6 of the worksheet named 'Merge Cells'.

Effects of Executing Macro Example

The following GIF illustrates the results of executing this macro example. As expected, cells A5 to E6 are merged.

#2: Unmerge Cells

VBA Code to Unmerge Cells

To unmerge cells with VBA, use a statement with the following structure:

Process Followed by VBA Code

VBA Statement Explanation

• Item: Worksheet.
  
  
  • VBA Construct: Workbook.Worksheets property.
    
    
  • Description: Returns a Worksheet object representing the worksheet you work with.
    
    
• Item: Range('A1CellReference').
  
  
  • VBA Construct: Worksheet.Range property.
    
    
  • Description: Returns a Range object representing a cell within the merged cell you unmerge. Specify the cell using an A1-style cell reference (A1CellReference) enclosed within quotations ('').
    
    
• Item: UnMerge.
  
  
  • VBA Construct: Range.UnMerge method.
    
    
  • Description: Separates the merged cell containing the cell you specify in item #2 above into individual regular cells.

Macro Example

Buildbox indie game engine. The following macro unmerges the merged cell containing cell C6 of the worksheet named 'Merge Cells'.

Effects of Executing Macro Example

The following GIF illustrates the results of executing this macro example. As expected, the merged cell containing cell C6 is unmerged into individual regular cells.

The merged cell range (A5 to E6) was originally merged using the macro example #1 above.

#3: Merge Cells and Center

VBA Code to Merge Cells and Center

To merge cells and center the contents with VBA, use a macro with the following statement structure:

Process Followed by VBA Code

VBA Statement Explanation

Lines #1 and #5: With Worksheet.Range('FirstCell:LastCell') | End With

1. Item: With… End With.
   
   
   • VBA Construct: With… End With statement.
     
     
   • Description: Statements within the With… End With statement (lines #2 through #4 below) are executed on the Range object returned by item #3 below.
     
     
2. Item: Worksheet.
   
   
   • VBA Construct: Workbook.Worksheets property.
     
     
   • Description: Returns a Worksheet object representing the worksheet you work with.
     
     
3. Item: Range('FirstCell:LastCell').
   
   
   • VBA Construct: Worksheet.Range property.
     
     
   • Description: Returns a Range object representing the cell range between FirstCell and LastCell. This is the cell range you merge.
     
     Specify FirstCell and LastCell using an A1-style cell reference. Separate FirstCell and LastCell using the range operator, a colon (:). Enclose the entire cell range address within quotations ('').

Line #2: .HorizontalAlignment = xlCenter

1. Item: HorizontalAlignment = xlCenter.
   
   
   • VBA Construct: Range.HorizontalAlignment property.
     
     
   • Description: Horizontally centers the contents of the cell range you specify in line #1 above by setting the HorizontalAlignment property to xlCenter.

Line #3: VerticalAlignment = xlCenter

1. Item: VerticalAlignment = xlCenter.
   
   
   • VBA Construct: Range.VerticalAlignment property.
     
     
   • Description: Vertically centers the contents of the cell range you specify in line #1 above by setting the VerticalAlignment property to xlCenter.

Line #4: Merge

1. Item: Merge.
   
   
   • VBA Construct: Range.Merge method.
     
     
   • Description: Merges the cells represented by the Range object you specify in line #1 above to create a merged cell.

Macro Example

The following macro (i) centers the contents in cells A8 to E9 of the worksheet named 'Merge Cells', and (ii) merges those cells.

How Do You Combine Data From Two Cells In Excel

Effects of Executing Macro Example

The following GIF illustrates the results of executing this macro example. As expected, VBA merges cells A8 to E9 and centers the contents.

#4: Merge Cells Across

VBA Code to Merge Cells Across

To merge cells across (in the same row) with VBA, use a statement with the following structure:

Process Followed by VBA Code

VBA Statement Explanation

• To lock in the lookup value in cell B1, insert $ signs before the column and the row:
  =VLOOKUP($B$1,'[Spreadsheet2.xlsx]SheetName'!$B$1:$E:$11,3,FALSE)
• To lock in the column only, insert a $ before B only.
• To lock in the row only, insert a $ before 1 only.
  

Need More Information or Help?

Merge Data From Two Cells In Excel Without

In this VBA Tutorial, you learn how to merge cells and unmerge cells in a variety of ways.

This VBA Tutorial is accompanied by Excel workbooks containing the data and macros I use in the examples below. You can get immediate free access to these example workbooks by subscribing to the Power Spreadsheets Newsletter.

Use the following Table of Contents to navigate to the section you're interested in.

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

• General VBA constructs and structures:
  
  
  • Learn about using variables here.
    
    
  • Learn about VBA data types here.
    
    
  • Learn about R1C1 and A1 style references here.
    
    
• Practical VBA applications and macro examples:
  
  
  • Learn how to work with worksheets here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Merge Cells

VBA Code to Merge Cells

To merge cells with VBA, use a statement with the following structure:

Process Followed by VBA Code

VBA Statement Explanation

1. Item: Worksheet.
   
   
   • VBA Construct: Workbook.Worksheets property.
     
     
   • Description: Returns a Worksheet object representing the worksheet you work with.
     
     
2. Item: Range('FirstCell:LastCell').
   
   
   • VBA Construct: Worksheet.Range property.
     
     
   • Description: Returns a Range object representing the cell range between FirstCell and LastCell. This is the cell range you merge.
     
     Specify FirstCell and LastCell using an A1-style cell reference. Separate FirstCell and LastCell using the range operator, a colon (:). Enclose the entire cell range address within quotations ('').
     
     
3. Item: Merge.
   
   
   • VBA Construct: Range.Merge method.
     
     
   • Description: Merges the cells represented by the Range object you specify in item #2 above to create a merged cell.

Macro Example

The following macro merges cells A5 to E6 of the worksheet named 'Merge Cells'.

Effects of Executing Macro Example

The following GIF illustrates the results of executing this macro example. As expected, cells A5 to E6 are merged.

#2: Unmerge Cells

VBA Code to Unmerge Cells

To unmerge cells with VBA, use a statement with the following structure:

Process Followed by VBA Code

VBA Statement Explanation

• Item: Worksheet.
  
  
  • VBA Construct: Workbook.Worksheets property.
    
    
  • Description: Returns a Worksheet object representing the worksheet you work with.
    
    
• Item: Range('A1CellReference').
  
  
  • VBA Construct: Worksheet.Range property.
    
    
  • Description: Returns a Range object representing a cell within the merged cell you unmerge. Specify the cell using an A1-style cell reference (A1CellReference) enclosed within quotations ('').
    
    
• Item: UnMerge.
  
  
  • VBA Construct: Range.UnMerge method.
    
    
  • Description: Separates the merged cell containing the cell you specify in item #2 above into individual regular cells.

Macro Example

Buildbox indie game engine. The following macro unmerges the merged cell containing cell C6 of the worksheet named 'Merge Cells'.

Effects of Executing Macro Example

The following GIF illustrates the results of executing this macro example. As expected, the merged cell containing cell C6 is unmerged into individual regular cells.

The merged cell range (A5 to E6) was originally merged using the macro example #1 above.

#3: Merge Cells and Center

VBA Code to Merge Cells and Center

To merge cells and center the contents with VBA, use a macro with the following statement structure:

Process Followed by VBA Code

VBA Statement Explanation

Lines #1 and #5: With Worksheet.Range('FirstCell:LastCell') | End With

1. Item: With… End With.
   
   
   • VBA Construct: With… End With statement.
     
     
   • Description: Statements within the With… End With statement (lines #2 through #4 below) are executed on the Range object returned by item #3 below.
     
     
2. Item: Worksheet.
   
   
   • VBA Construct: Workbook.Worksheets property.
     
     
   • Description: Returns a Worksheet object representing the worksheet you work with.
     
     
3. Item: Range('FirstCell:LastCell').
   
   
   • VBA Construct: Worksheet.Range property.
     
     
   • Description: Returns a Range object representing the cell range between FirstCell and LastCell. This is the cell range you merge.
     
     Specify FirstCell and LastCell using an A1-style cell reference. Separate FirstCell and LastCell using the range operator, a colon (:). Enclose the entire cell range address within quotations ('').

Line #2: .HorizontalAlignment = xlCenter

1. Item: HorizontalAlignment = xlCenter.
   
   
   • VBA Construct: Range.HorizontalAlignment property.
     
     
   • Description: Horizontally centers the contents of the cell range you specify in line #1 above by setting the HorizontalAlignment property to xlCenter.

Line #3: VerticalAlignment = xlCenter

1. Item: VerticalAlignment = xlCenter.
   
   
   • VBA Construct: Range.VerticalAlignment property.
     
     
   • Description: Vertically centers the contents of the cell range you specify in line #1 above by setting the VerticalAlignment property to xlCenter.

Line #4: Merge

1. Item: Merge.
   
   
   • VBA Construct: Range.Merge method.
     
     
   • Description: Merges the cells represented by the Range object you specify in line #1 above to create a merged cell.

Macro Example

The following macro (i) centers the contents in cells A8 to E9 of the worksheet named 'Merge Cells', and (ii) merges those cells.

How Do You Combine Data From Two Cells In Excel

Effects of Executing Macro Example

The following GIF illustrates the results of executing this macro example. As expected, VBA merges cells A8 to E9 and centers the contents.

#4: Merge Cells Across

VBA Code to Merge Cells Across

To merge cells across (in the same row) with VBA, use a statement with the following structure:

Process Followed by VBA Code

VBA Statement Explanation

1. Item: Worksheet.
   
   
   • VBA Construct: Workbook.Worksheets property.
     
     
   • Description: Returns a Worksheet object representing the worksheet you work with.
     
     
2. Item: Range('FirstCell:LastCell').
   
   
   • VBA Construct: Worksheet.Range property.
     
     
   • Description: Returns a Range object representing the cell range between FirstCell and LastCell. This is the cell range you merge.
     
     Specify FirstCell and LastCell using an A1-style cell reference. Separate FirstCell and LastCell using the range operator, a colon (:). Enclose the entire cell range address within quotations ('').
     
     
3. Item: Merge.
   
   
   • VBA Construct: Range.Merge method.
     
     
   • Description: Merges the cells in each row of the cell range you specify in item #2 above to create separate merged cells. For these purposes, considers the Across parameter (item #4 below).
     
     
4. Item: Across:=True.
   
   
   • VBA Construct: Across parameter of the Range.Merge method.
     
     
   • Description: Specifies that the cells in each row of the cell range you specify in item #2 above are merged separately. In other words, the cells in each row are merged into separate merged cells (vs. a single merged cell covering the entire cell range).
     
     The default value of the Across parameter is False. In such case, all cells within the cell range you specify are merged into a single cell. This is the equivalent of simply merging cells (operation #1 above).

Macro Example

The following macro merges cells A11 to E15 of the worksheet named 'Merge Cells' across. Therefore, the cells in each row from row 11 to row 15 are merged into separate merged cells.

Effects of Executing Macro Example

The following GIF illustrates the results of executing this macro example. As expected, VBA merges cells A11 to E15 across.

#5: Merge Cells Based on Cell Value

VBA Code to Merge Cells Based on Cell Value

To merge cells based on a cell value (whether it meets certain criteria), use a macro with the following statement structure:

Process Followed by VBA Code

VBA Statement Explanation

Lines #1 and #5: With Worksheet | End With

1. Item: With… End With.
   
   
   • VBA Construct: With… End With statement.
     
     
   • Description: Statements within the With… End With statement (lines #2 through #4 below) are executed on the Worksheet object returned by item #2 below.
     
     
2. Item: Worksheet.
   
   
   • VBA Construct: Workbook.Worksheets property.
     
     
   • Description: Returns a Worksheet object representing the worksheet you work with.

Lines #2 and #4: For Counter = LastRow To FirstRow Step -1 | Next Counter

1. Item: For… Next Counter.
   
   
   • VBA Construct: For… Next statement.
     
     
   • Description: Repeats the statements within the loop (line #3 below) for each row between FirstRow (item #4 below) and LastRow (item #3 below).
     
     
2. Item: Counter.
   
   
   • VBA Construct: Counter of For… Next statement.
     
     
   • Description: Loop counter. If you explicitly declare a variable to represent the loop counter, use the Long data type.
     
     
3. Item: LastRow.
   
   
   • VBA Construct: Counter Start of For… Next statement.
     
     
   • Description: Number of the last row (further down the worksheet) you want the macro to consider when identifying rows to merge cells. The number of the last row is also the initial value of Counter (item #2 above).
     
     If you explicitly declare a variable to represent the number of the last row to consider, use the Long data type.
     
     
4. Item: FirstRow.
   
   
   • VBA Construct: Counter End of For… Next statement.
     
     
   • Description: Number of the first row (closer to the top of the worksheet) you want the macro to consider when identifying rows to merge cells. The number of the first row is also the final value of Counter (item (#2 above).
     
     If you explicitly declare a variable to represent the number of the first row to consider, use the Long data type.
     
     
5. Item: Step -1.
   
   
   • VBA Construct: Step of For… Next statement.
     
     
   • Description: Amount by which Counter (item #2 above) changes every time a loop iteration occurs.
     
     In this scenario, you loop backwards: from LastRow (item #3 above) to FirstRow (item #4 above). Therefore, step is -1.

Line #3: If .Cells(Counter, CriteriaColumn).Value = Criteria Then .Range(.Cells(Counter, FirstColumn), .Cells(Counter, LastColumn)).Merge

1. Item: If… Then.
   
   
   • VBA Construct: If… Then… Else statement.
     
     
   • Description: Conditionally executes the statement at the end of the line of code (items #5 through #8 below) if the condition specified in item #4 below is met.
     
     
2. Item: .Cells(Counter, CriteriaColumn).
   
   
   • VBA Construct: Worksheet.Cells property and Range.Item property.
     
     
   • Description: Returns a Range object representing the cell at the intersection of row number Counter and column number CriteriaColumn.
     
     At any given time, the value of the loop counter (Counter) is the same as that of the row through which the macro is currently looping. CriteriaColumn is the number of the column containing the cells you consider for purposes of determining whether to merge cells in the row through which the macro is currently looping.
     
     
3. Item: Value.
   
   
   • VBA Construct: Range.Value property.
     
     
   • Description: Returns the value of the cell represented by the Range object returned by item #2 above.
     
     
4. Item: .Cells(Counter, CriteriaColumn).Value = Criteria.
   
   
   • VBA Construct: Condition of If… Then… Else statement.
     
     
   • Description: This condition is an expression that evaluates to True or False, as follows:
     
     
     • • True: When the value of the cell represented by the Range object returned by item #2 above is equal to the criteria you specify (Criteria).
         
         
       • False: When the value of the cell represented by the Range object returned by item #2 above isn't equal to the criteria you specify (Criteria).
         
         

     If you explicitly declare a variable to represent value, ensure that the data type you use can handle the value you use as criteria.
     
     

5. Item: .Range.
   
   
   • VBA Construct: Worksheet.Range property.
     
     
   • Description: Returns a Range object representing a cell range specified as follows:
     
     
     • Leftmost cell: Range object returned by item #6 below.
       
       
     • Rightmost cell: Range object returned by item #7 below.
       
       
6. Item: .Cells(Counter, FirstColumn).
   
   
   • VBA Construct: Worksheet.Cells property and Range.Item property.
     
     
   • Description: Returns a Range object representing the cell at the intersection of row number Counter and column number FirstColumn.
     
     At any given time, the value of the loop counter (Counter) is the same as that of the row through which the macro is currently looping. FirstColumn is the number of the first column in the cell range you want the macro to merge. If you explicitly declare a variable to represent FirstColumn, use the Long data type.
     
     
7. Item: .Cells(Counter, LastColumn).
   
   
   • VBA Construct: Worksheet.Cells property and Range.Item property.
     
     
   • Description: Returns a Range object representing the cell at the intersection of row number Counter and column number LastColumn.
     
     At any given time, the value of the loop counter (Counter) is the same as that of the row through which the macro is currently looping. LastColumn is the number of the last column in the cell range you want the macro to merge. If you explicitly declare a variable to represent LastColumn, use the Long data type.
     
     
8. Item: Merge.
   
   
   • VBA Construct: Range.Merge method.
     
     
   • Description: Merges the cells represented by the Range object returned by items #5 through #7 above to create a merged cell.

Macro Example

The following macro merges cells in columns myFirstColumn through myLastColumn in each row between myFirstRow and myLastRow where the value stored in column myCriteriaColumn is the string held by the myCriteria variable.

• myFirstRow is set to 5.
  
  
• myLastRow is set to the number of the last row with data in the worksheet named 'Merge Cells Based on Criteria'. The constructs used by the statement that finds the last row with data in the worksheet are the Worksheet.Cells property, the Range.Find method, and the Range.Row property.
  
  
• Both myCriteriaColumn and myFirstColumn are set to 1 (column A).
  
  
• myLastColumn is set to 5 (column E).
  
  
• myCriteria holds the string 'Merge cells'

Effects of Executing Macro Example

The following GIF illustrates the results of executing this macro example. As expected, VBA merges cells in columns A through E in each row where the value stored in column A is the string 'Merge Cells'.

#6: Merge Cells Within a Row Based on Cell Value

VBA Code to Merge Cells Within a Row Based on Cell Value

To merge cells within a row based on a cell value (the cell value determines the number of cells to merge), use a macro with the following statement structure:

Process Followed by VBA Code

VBA Statement Explanation

Lines #1 and #5: With Worksheet | End With

1. Item: With… End With.
   
   
   • VBA Construct: With… End With statement.
     
     
   • Description: Statements within the With… End With statement (lines #2 through #4 below) are executed on the Worksheet object returned by item #2 below.
     
     
2. Item: Worksheet.
   
   
   • VBA Construct: Workbook.Worksheets property.
     
     
   • Description: Returns a Worksheet object representing the worksheet you work with.

Lines #2 and #4: For Counter = LastRow To FirstRow Step -1 | Next Counter

1. Item: For… Next Counter.
   
   
   • VBA Construct: For… Next statement.
     
     
   • Description: Repeats the statements within the loop (line #3 below) for each row between FirstRow (item #4 below) and LastRow (item #3 below).
     
     
2. Item: Counter.
   
   
   • VBA Construct: Counter of For… Next statement.
     
     
   • Description: Loop counter. If you explicitly declare a variable to represent the loop counter, use the Long data type.
     
     
3. Item: LastRow.
   
   
   • VBA Construct: Counter Start of For… Next statement.
     
     
   • Description: Number of the last row (further down the worksheet) you want the macro to consider when identifying rows to merge cells. The number of the last row is also the initial value of Counter (item #2 above).
     
     If you explicitly declare a variable to represent the number of the last row to consider, use the Long data type.
     
     
4. Item: FirstRow.
   
   
   • VBA Construct: Counter End of For… Next statement.
     
     
   • Description: Number of the first row (closer to the top of the worksheet) you want the macro to consider when identifying rows to merge cells. The number of the first row is also the final value of Counter (item (#2 above).
     
     If you explicitly declare a variable to represent the number of the first row to consider, use the Long data type.
     
     
5. Item: Step -1.
   
   
   • VBA Construct: Step of For… Next statement.
     
     
   • Description: Amount by which Counter (item #2 above) changes every time a loop iteration occurs.
     
     In this scenario, you loop backwards: from LastRow (item #3 above) to FirstRow (item #4 above). Therefore, step is -1.

Line #3: .Cells(Counter, BaseColumn).Resize(ColumnSize:=.Cells(Counter, SizeColumn).Value).Merge

1. Item: .Cells(Counter, BaseColumn).
   
   
   • VBA Construct: Worksheet.Cells property and Range.Item property.
     
     
   • Description: Returns a Range object representing the cell at the intersection of row number Counter and column number BaseColumn.
     
     At any given time, the value of the loop counter (Counter) is the same as that of the row through which the macro is currently looping. BaseColumn is the number of the column you use as base for purposes of merging cells within the row through which the macro is currently looping.
     
     
2. Item: Resize(ColumnSize:=.Cells(Counter, SizeColumn).Value).
   
   
   • VBA Construct: Range.Resize property.
     
     
   • Description: Returns a Range object representing a resized cell range. The Range object returned by Range.Resize is determined as follows:
     
     
     • Base Cell Range: The base Range object (prior to resizing) is that returned by item #1 above.
       
       
     • Row Size: The number of rows in the cell range returned by Range.Resize remain the same. In other words, the cell range where cells are merged is 1 row tall.
       
       This is because the first parameter of Resize (known as RowSize) is omitted. Therefore, the number of rows in the cell range remains the same.
       
       
     • Column Size: The number of columns in the cell range returned by Range.Resize is determined by item #3 below.
       
       
3. Item: ColumnSize:=.Cells(Counter, SizeColumn).Value.
   
   
   • VBA Constructs: ColumnSize parameter of Range.Resize property and Range.Value property.
     
     
   • Description: Specifies the number of columns in the Range object returned by the Range.Resize property. The number of columns in this cell range is equal to the value within the cell at the intersection of row number Counter and column number SizeColumn (.Cells(Counter, SizeColumn).Value).
     
     At any given time, the value of the loop counter (Counter) is the same as that of the row through which the macro is currently looping. SizeColumn is the number of the column containing the number of cells you want to merge within the row through which the macro is currently looping.
     
     
4. Item: Merge.
   
   
   • VBA Construct: Range.Merge method.
     
     
   • Description: Merges the cells represented by the Range object returned by items #1 through #3 above.

Macro Example

The following macro merges a certain number of cells, starting with the cell in column myBaseColumn, in each row between myFirstRow and myLastRow. The number of merged cells is equal to the value stored in mySizeColumn. If that value is 1, no cells are merged.

In other words, the macro merges the cells between column number mySizeColumn and the column whose number is equal to that stored within the cell in myBaseColumn.

• myFirstRow is set to 5.
  
  
• myLastRow is set to the number of the last row with data in the worksheet named 'Merge Cells Based on Cell Value'. The constructs used by the statement that finds the last row with data in the worksheet are the Worksheet.Cells property, the Range.Find method, and the Range.Row property.
  
  
• Both myBaseColumn and mySizeColumn are set to 1 (column A).

Effects of Executing Macro Example

The following GIF illustrates the results of executing this macro example. As expected, for each row with data, the macro merges the cells between column A and the column whose number is specified in column A.

References to VBA Constructs Used in this VBA Tutorial

Use the following links to visit the appropriate webpage within the Microsoft Office Dev Center:

1. Identify the worksheet you work with:
   
   
   • Workbook.Worksheets property.
     
     
2. Return a Range object representing the cells you merge:
   
   
   • Worksheet.Range property.
     
     
   • Worksheet.Cells property.
     
     
   • Range.Resize property.
     
     
3. Merge cells:
   
   
   • Range.Merge method.
     
     
4. Unmerge cells:
   
   
   • Range.UnMerge method.
     
     
5. Center the contents of a cell range horizontally or vertically:
   
   
   • Range.HorizontalAlignment property.
     
     
   • Range.VerticalAlignment property.
     
     
6. Identify last row with data in a worksheet:
   
   
   • Range.Find method.
     
     
   • Range.Row property.
     
     
7. Loop through rows:
   
   
   • For… Next statement.
     
     
8. Identify the value stored in a cell to specify criteria for merging cells.
   
   
   • Range.Value property.
     
     
9. Test if cells meet criteria for merging:
   
   
   • If… Then… Else statement.
     
     
10. Work with variables:
    
    
    • Dim statement.
      
      
    • Set statement.
      
      
    • Data types:
      
      
      • Long data type.
        
        
      • String data type.
        
        
11. Simplify object references:
    
    
    • With… End With statement.