Merge the same cells in Excel using vbs

Assume there is a table like this

You need to merge the same cells in different rows to achieve the following effect:

 

Follow these steps to use vbs

1. Sort the table first. This column is also the column referenced when the cells are merged, usually the student ID or ID column.

2. press Alt + F11 in the current Excel file to bring up the vbs editor and paste the following code

Sub mergerow ()
'Destare Variables
Dim lrow as double 'last row
Dim srow as double 'current row
Dim cvalue as string 'value used to compare

'The reference column
Dim refcol as integer
Refcol = 2

'The columns You Want To mergen
Dim mercol as integer
Mercol = 2

Application. displayalerts = false
Application. screenupdating = false

With activesheet

'Init the variables
Lrow =. Range ("a1048576"). End (xlup). Row
Srow = lrow
Cvalue =. cells (lrow, refcol). Value

For I = lrow-1 to 1 step-1
Msgbox (. cells (I, refcol). value)
Msgbox (. cells (I, refcol). value = cvalue)
If. cells (I, refcol). value = cvalue then
Else
'Only merge two or more cells
If srow-I> 1 then
For j = 1 to mercol Step 1
. Range (. cells (I + 1, J),. cells (srow, J). Merge
Next
End if

'Reset the variables
Srow = I
Cvalue =. cells (I, refcol). Value
End if
Next

End

Application. displayalerts = true
Application. screenupdating = true
End sub

3. Then press F5 to run

For tables in other formats, you only need to modify the following two parameters:

Refcol is the column referenced during merge. If your student ID column is the third column, you can set this value to 3.

'The reference column
Dim refcol as integer
Refcol =3

Mercol is the number of columns you intend to merge. If your student ID column is followed by a gender column, you can set this value to 3.
'The columns You Want To mergen
Dim mercol as integer
Mercol =3

Merge the same cells in Excel using vbs