VBA Error 1004 (Bizzare)

grabrail

Board Regular
Joined
Sep 6, 2010
Messages
53
I have a spreadsheet with some VBA code in the background. The code selects a range of cells and sorts them based on the value of an accompanying cell.

On my system it works fine, no errors. on my colleagues system it errors with 'error 1004: to do this all the merged cells need to be the same size'

one of the columns in the selected data is made up of merged cells, all selected are the exact same size.

code:

VBA Code:
Private Sub CommandButton1_Click()

 Range("A50:J78").Select
    ActiveWorkbook.Worksheets("VI Sheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("VI Sheet").Sort.SortFields.Add Key:=Range( _
        "L50:L78"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("VI Sheet").Sort
        .SetRange Range("A49:L78")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A47").Select

The selected range is in the red box in the image below, this goes from Cell A50 to J78 and each row is identical all the way down

Capture.PNG


when my button is clicked that runs the code, i get the following error:

error.PNG


clicking debug takes me to the .Apply line in the code above

If I unmerge all the cells in the 3rd column, the code runs fine, but my data placement is all messed up then.

Like I say, this works absolutely fine on my system, but when running on a different computer i get the error, both running same version of Excel.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,939
Office Version
  1. 365
Platform
  1. Windows
Instead of merging C:G use the Center across selection alignment option.

That should stop you getting the merged cell errors.

Not sure about the data placement, what's the problem there when you unmerge the cells?
 

grabrail

Board Regular
Joined
Sep 6, 2010
Messages
53
Instead of merging C:G use the Center across selection alignment option.

That should stop you getting the merged cell errors.

Not sure about the data placement, what's the problem there when you unmerge the cells?
Thanks, but it doesnt make any sense to me why it works ok on one system but not another
 

Watch MrExcel Video

Forum statistics

Threads
1,114,513
Messages
5,548,493
Members
410,840
Latest member
Kar3ousse
Top