Excel VBA: Autofilling with dynamic range error

Question

I am writing small code to automate some of my work. I am using Autofill to fill range. Everything works ok as long as the range stays the same. When I change range it shows error 1004: This operation requires the merged cells to be identically sized.

One more thing to add, it shows only when range becomes bigger.

Here is part of my code, when range changes line below 'autofill first column becomes yellow:

Dim WSL As Worksheet, WSB As Worksheet
Dim first_col As Long, second_col As Long
Dim first_r As Byte, first_c As Byte
Dim second_r As Byte, second_c As Byte
Dim LastCellRowNumber As Long
Dim LastCell As Range, ActiveWS As String

Application.ScreenUpdating = False

Set WSB = Worksheets("Barcodes") 'your worksheet name
Set WSL = Worksheets("List") 'your worksheet name
With WSL
    Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
    LastCellRowNumber = LastCell.Row
End With

ActiveWS = ActiveSheet.Name

first_col = Round(LastCellRowNumber / 2) + 1
second_col = LastCellRowNumber - first_col

first_r = 5 'position of "first column" row
first_c = 7 'position of "first column" column
second_r = 5 'position of "second column" row
second_c = 11 'position of "first column" column

j = 7
k = 8
l = 7
m = 8

'activate Barcodes sheet
WSB.Activate

'autofill first column
WSB.Range(Cells(first_r, first_c), Cells(first_r + 1, first_c)).AutoFill _
Destination:=Range(Cells(first_r, first_c), Cells((first_r + 1) + (first_col * 2) - 4, _
first_c)), Type:=xlFillDefault 'filling column G

WSB.Range(Cells(first_r, first_c + 1), Cells(first_r + 1, first_c + 2)).AutoFill _
Destination:=Range(Cells(first_r, first_c + 1), Cells(first_r + 1 + (first_col * 2) - 4, _
first_c + 2)), Type:=xlFillFormats 'filling with columns H:I

'autofill second column
WSB.Range(Cells(second_r, second_c), Cells(second_r + 1, second_c)).AutoFill _
Destination:=Range(Cells(second_r, second_c), Cells(second_r + 1 + (second_col * 2), _
second_c)), Type:=xlFillDefault 'filling column K

WSB.Range(Cells(second_r, second_c + 1), Cells(second_r + 1, second_c + 2)).AutoFill _
Destination:=Range(Cells(second_r, second_c + 1), Cells(second_r + 1 + (second_col * 2), _
second_c + 2)), Type:=xlFillFormats 'filling with columns L:M

@simoco WSB contains merged cells - they are templates. Autofill selects those merged cells and autofilling given range. — lowak, Feb 18 '14 at 10:02
as your error message says, you have merged cells with different "shape". say in first row `C1:D1` merged, but in second `D2:E2` — Dmitry Pavliv, Feb 18 '14 at 10:04
@simoco Yeah but it is strange... let's sau this `C1:D1` is different than other cells. That's why I use autofill to make them identical - and this works ok. But when range is change to merge two more cells together it shows this error. — lowak, Feb 18 '14 at 10:13
I suggest you at first make `range("C200:D200").UnMerge` to unmerge all cells in destination range (except first row) — Dmitry Pavliv, Feb 18 '14 at 10:20
@simoco, I selected range that is about to be autofilled, unmerge it before autofill and it works perfectly. Does not matter if range is bigger or lower then original one. Thanks simoco, would like to accept your anwser but it is just comment ;) — lowak, Feb 18 '14 at 10:32

score 0 · Accepted Answer · edited May 23 '17 at 10:31

Follow up from comments:

As your error message says, you have merged cells with different "shape". Say in first row C1:D1 merged, but in second D2:E2. I suggest you at first make range("C200:D200").UnMerge to unmerge all cells in destination range (except first row).

Something like this should work (just suit your ranges to correct one)

With wbs
    'unmerge all cells
    .Range(.Cells(first_r + 1, first_c), .Cells((first_r + 1) + (first_col * 2) - 4, _
        first_c)).UnMerge

    'autofill first column
    .Range(.Cells(first_r, first_c), .Cells(first_r + 1, first_c)).AutoFill _
    Destination:=.Range(.Cells(first_r, first_c), .Cells((first_r + 1) + (first_col * 2) - 4, _
        first_c)), Type:=xlFillDefault 'filling column G
End With

Btw, one more tip for you: how to avoid using select/active statements

Yeah, I wrote something similar. Thanks for "avoid using select/active" tip. I read pretty often so I try to avoid it. My comment was unfortunate for use of word "select" :) Thanks for help once again! — lowak, Feb 18 '14 at 10:46

Excel VBA: Autofilling with dynamic range error

1 Answers1