VBA Merge & Center The Adjacent Rows With Same Data
➤ If there are same data in the adjacent rows, the same cells
are merged into one cell and centered.
In this way, the data looks more neat .
Our macro to merge and center cells :
Sub merge_center()
Dim i, a As Long, one, two As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For i = 2 To Cells(Rows.Count, "A").End(3).Row
If Cells(i, "A") = Cells(i + 1, "A") Then
one = Cells(i, "A").Address
For a = i + 1 To Cells(Rows.Count, "A").End(3).Row
If Cells(a, "A") <> Cells(a + 1, "A") Then
two = Cells(a, "A").Address
Exit For
End If
Next a
Range(one & ":" & two).merge ' Same cells are merged
Range(one & ":" & two).Interior.ColorIndex = 34 ' Background is painted
Else
End If
Range("A2:A" & i).HorizontalAlignment = xlCenter
Range("A2:A" & i).VerticalAlignment = xlCenter
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Popup menu on textbox control is created using class module.The menu can be activated from the MouseUp event of the
textbox:
Private WithEvents TControl As MSForms.TextBox
Private Sub TControl_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Button = 2 And Shift = 0 Then
Run "RightClickMenuTextBox"
End If
End Sub
These operations can be performed with the right click menu
:
With Application
.ScreenUpdating = False
.DisplayAlerts = False
On Error Resume Next
MkDir MyFilePath
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
End With
.SaveAs Filename:=MyFilePath _
& "\" & SheetName & ".xlsx"
.Close SaveChanges:=True
End With
.CutCopyMode = False
Next
End With
Sheets(1).Activate
Application.ScreenUpdating = True
MsgBox "The Process Was Carried Out Successfully", vbInformation
End Sub
Trigger Macro Or VBA Code When A Specific Cell Is Selected
In this Excel template, the background color of the cells was
painted different colors with macros.
When cells of specific range in "Example" worksheet (between AR4 and AR28 cells) are selected,the macros are triggered .For this purpose, we have added the following codes to the page's Worksheet_SelectionChange method :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$AR$4": Call col_one
Case "$AR$5": Call col_two
Case "$AR$6": Call col_three
Case "$AR$7": Call col_four
Case "$AR$8": Call col_five
Case "$AR$9": Call col_six
Case "$AR$10": Call col_seven
Case "$AR$11": Call col_eight
Case "$AR$12": Call col_nine
Case "$AR$13": Call col_ten
Case "$AR$14": Call col_eleven
Case "$AR$15": Call col_twelve
Case "$AR$16": Call col_thirteen
Case "$AR$17": Call col_fourteen
Case "$AR$18": Call col_fifteen
Case "$AR$19": Call col_sixteen
Case "$AR$20": Call col_seventeen
Case "$AR$21": Call col_eighteen
Case "$AR$22": Call col_nineteen
Case "$AR$23": Call col_twenty
Case "$AR$24": Call col_twentyone
Case "$AR$25": Call col_twentytwo
Case "$AR$26": Call col_twentythree
Case "$AR$27": Call col_twentyfour
Case "$AR$28": Call col_twentyfive
End Select
End Sub
The Call statement was used to call a Sub procedure.