Hi Question About VBA code for Sorting a-z just column B, but i have a few merged cells

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,968
Office Version
  1. 365
Platform
  1. Windows
HI I want to sort column B down in a-z sort. From B4 to B190. My problem is from B1 to B3 is merged cells and I need to leave it. I need to work around so I can sort the rows below B4 and down without get the error. This operation requires the merged cells to be identically sized.
An vba code on this would help thanks.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

kit99

Active Member
Joined
Mar 17, 2015
Messages
352
HI I want to sort column B down in a-z sort. From B4 to B190. My problem is from B1 to B3 is merged cells and I need to leave it. I need to work around so I can sort the rows below B4 and down without get the error. This operation requires the merged cells to be identically sized.
An vba code on this would help thanks.

Hi.
Must point out that I'm a newbie to vba..., but if you only want to sort B4:B190 (same range every time), and want to do this with a vba, this will work:
Code:
Sub AtoZ()


        With ActiveSheet.Sort
            .SetRange Range("B4:B190")
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
End Sub
 
Last edited:

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,968
Office Version
  1. 365
Platform
  1. Windows
It didn't do anything. that column blinked and it didn't debug which is good, but it didn't sort it either. Remember from B1 to B3 I have those columns merged. not sure if that's giving the issue. thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Merged cells are the spawn of the devil. Avoid them at all costs! They cause all sorts of issues with things like VBA and sorting.

If you really need to keep it, just temporarily unmerge them long enough to do your sort.

The code you need can be gotten pretty easily with the Macro Recorder. Just turn on the Macro recorder and record your self doing the following steps:
1. Unmerge cells B1:B3
2. Sort B4:B190
3. Merge cells B1:B3

That should give you the code you need.
 

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,968
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

actually forget that I unmerged the cells in B column and I still get a pop up saying that it is merged.
 

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,968
Office Version
  1. 365
Platform
  1. Windows
I don't know whats up on this gonna open a new book and test column a or b on there instead of undoing stuff here. Just not working for me.
 

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,968
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I just tried a new book B4 to B7 replaced numbers in your code. I get no error but it still doesn't sort. Has to be something missing here? Let me know if you think of anything.
 

kit99

Active Member
Joined
Mar 17, 2015
Messages
352
actually forget that I unmerged the cells in B column and I still get a pop up saying that it is merged.

My simple vba further up was done with B1:B3 merged, and it worked just fine...
The code was also made by using the macro recorder mentioned by Joe4. It is recommended.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
actually forget that I unmerged the cells in B column and I still get a pop up saying that it is merged.
Probably means that you have merged cells elsewhere on your sheet.

Can you post the code you are running, and sample data for cells B4:B7 so I can try to recreate exactly what you are trying to do, and the data you are working with?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,834
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
If you run the code below does the message box show any cells from column B?

Code:
Sub FindAllMerged()
    Dim c As Range, sMsg As String
    sMsg = ""
    For Each c In ActiveSheet.UsedRange
        If c.MergeCells Then
            If sMsg = "" Then
                sMsg = "Merged worksheet cells:" & vbCr
            End If
            sMsg = sMsg & Replace(c.Address, "$", "") & vbCr
        End If
    Next
    If sMsg = "" Then
        sMsg = "No Merged Cells Found."
    End If
    MsgBox sMsg
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top