Cannot get macro button to sort with merged cells on sheet

lonniepoet · #1 01-25-2016, 09:10 PM

Greetings. I have a button on the ‘Floodcoat Workup_Combos’ tab that auto sorts Column C (“Grade”) so that 200# E is on the bottom and 200# B is on the top. These are the only types of board grade that will be entered onto this sheet. However, the users using this sheet have requested a button to auto sort. I know they could simply using the filter option, but they are requesting a button. I got this to happen on the ‘Floodcoat Workup_Combos’ tab which I created, but on another tab that a coworker made (‘Floodcoat Workup_Single), I cannot get the button to work due to cells being merged & centered. Is there any way to get this button to work without messing with my coworker’s formatting? If you run into protection issues on anything I do not have a password at this time by the way.

Thank you all in advance.

NoSparks · #2 01-26-2016, 09:13 AM

Does this do what you're asking ?
It's straight from the Macro Recorder.

Code:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B7:S45").Select
    ActiveWorkbook.Worksheets("Floodcoat Workup_Single").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Floodcoat Workup_Single").Sort.SortFields.Add Key _
        :=Range("E7:E45"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Floodcoat Workup_Single").Sort
        .SetRange Range("B7:S45")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B2:D4").Select
End Sub

lonniepoet · #3 01-26-2016, 08:59 PM

NoSparks,

Thank you. That code does exactly what I needed. I was using the Macro Recorder too but couldn’t get it to take the right way. Maybe it was too late and I was not seeing things properly. LOL

Thanks again
-Lonnie

lonniepoet · #4 02-04-2016, 04:33 PM

On the tab names 'Floodcoat Workup_Combos in the top left I have a button just like the last one I had worked on and I tried to manipulate the code from the button on the 'Floodcoat Workup_Single' tab, but I cannot get it to generate anything without a debug message. My goal is to not have the filter on. Any help would be much appreciated. Thank you.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA merging cells and counting Merged rows	Snaybot	Excel Programming	2	11-09-2015 03:56 AM
Merged cells - automatically expanding?	Suzy	Excel	1	08-26-2015 09:39 AM
Table will not allow sorting because "cells are merged". I can't find the merged cells.	wendyloooo	Word Tables	1	05-26-2015 01:19 PM
Accidentally merged cells?	Fraser	Excel	2	02-16-2014 10:15 PM
Debug for macro run through button only when sheet protected	leahca	Excel Programming	0	11-24-2011 04:47 AM