VBScript Merging Cells

I found some Vbscript on the internet to merge cells in an excel sheet I'm working in. It's currently working great. It takes a range of sorted data and then merges equivalent cells together. I currently have to input the range manually, and i'd rather have the functionality for excel to auto-determine that range for me. The column will always be column A, so i just need the range from A1:End of A.

Here is the code I'm working with:

Sub MergeCell()  Dim Rng As Range, xCell as Range  Dim xRows as Integer  Set WorkRng = Application.Selection  Set WorkRng = Application.InputBox("Range", WorkRng.Address, Type:=8)  Application.ScreenUpdating = False  Application.DisplayAlerts = False  xRows = WorkRng.Rows.Count  For Each Rng In WorkRng.Columns     For i = 1 To xRows -1        For j = i + 1 To xRows           If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then              Exit For           End If        Next        WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge        i = j - 1      Next  Next  Application.ScreenUpdating = True  Application.DisplayAlerts = True  End Sub  

What would be the best way to accomplish this?

https://stackoverflow.com/questions/66827337/vbscript-merging-cells March 27, 2021 at 09:57AM