Drag Formula Down to Cell 100,000

[Code Ghost]

Good morning community!

I have copied and modified the next VBA formula from a YouTube video:

Range("G2").Formula = "F2*0.2"
Range("G2").AutoFill Range("G2:G16")

The formula, of course, works just fine, copying the formula to cell G16.
I modified it as follows:

Sheet3.Range("I3").Formula = "=SUM(Q3:W3)+G3-E3"
Range("I3").AutoFill Range("I3:I100000")

In my wishing imagination, I was hoping this formula would copy the formula from cell I3 down to cell I100000.
However, I get an error 1004 message: This operation requires the merged cells to be identically sized
I am looking at my sheet and I don't have any cells merged.
I'll include an image below.
I could perhaps use some minutes to drag the formula down myself, but it would be nice to know how to do this.

Thank you in advance.

 

[Micron]

It would seem that you have merged cells but are just not seeing them. Formatting interior borders of merged cells can hide the merge, although I don't see why that would be done. Anyway AFAIK, you can check a range for merged cells in the immediate window like so
?IsNull(Sheets("MASTER").Range("E1:E10").mergecells)

If it returns True, you have merged cells somewhere. Maybe try that on your range and see what you get.
Or click on selector at top left of sheet and select all cells. Then check alignment tab on Format Cells Dialog (Ctrl+1). If the merged checkbox is dark you do have some merged cells somewhere. At this point you could click on it until it goes clear (may happen the first click) and un-merge if you don't want them.

 

[jolivanes]

Re: "I am looking at my sheet and I don't have any cells merged"
Could this possibly mean that if there are merged cells it is OK to unmerge?
If so, what happens if you insert this

Columns(9).UnMerge

before this

Sheet3.Range("I3").Formula = "=SUM(Q3:W3)+G3-E3"
Range("I3").AutoFill Range("I3:I100000")

Might be a good idea to try on a copy of your original.

 

[Code Ghost]

It would seem that you have merged cells but are just not seeing them. Formatting interior borders of merged cells can hide the merge, although I don't see why that would be done. Anyway AFAIK, you can check a range for merged cells in the immediate window like so
?IsNull(Sheets("MASTER").Range("E1:E10").mergecells)

If it returns True, you have merged cells somewhere. Maybe try that on your range and see what you get.
Or click on selector at top left of sheet and select all cells. Then check alignment tab on Format Cells Dialog (Ctrl+1). If the merged checkbox is dark you do have some merged cells somewhere. At this point you could click on it until it goes clear (may happen the first click) and un-merge if you don't want them.

Hi Micron,
The range pulls data from a different invoice or worksheet where cells are merged.
Perhaps that's the problem.
Meanwhile, I just dragged the formula down to row 50,000. It didn't take two minutes.
Thank you for taking the time to help.
Have a great day!

 

[Code Ghost]

Re: "I am looking at my sheet and I don't have any cells merged"
Could this possibly mean that if there are merged cells it is OK to unmerge?
If so, what happens if you insert this

Columns(9).UnMerge

before this

Sheet3.Range("I3").Formula = "=SUM(Q3:W3)+G3-E3"
Range("I3").AutoFill Range("I3:I100000")

Might be a good idea to try on a copy of your original.

Hi Jolivanes,
The range pulls data from a different invoice or worksheet where cells are merged.
Perhaps that's the problem.
Meanwhile, I just dragged the formula down to row 50,000. It didn't take two minutes.
Thank you for taking the time to help.
Have a great day!

 

[Alex Blakenburg]

The range pulls data from a different invoice or worksheet where cells are merged.
Perhaps that's the problem.

Merge cells on the other sheet won't cause the issue, only merged cells in your AutoFill range will cause the issue.

I just dragged the formula down to row 50,000

You don't need to do this.
• Go to the Name Box (Blank box in the top left corner just above the A for the first column)
• Type or copy in I3:I50000 and hit Enter
• Ctrl+D to Fill down

The fact this works indicates your merged cells are below I50000 because doing it manually if there are merged cells gives the same error message as VBA.
Why not just Unmerge the cells in the whole column as suggested by @jolivanes.

 

[Micron]

Why not just Unmerge the cells in the whole column as suggested by @jolivanes.

I gave two suggestions on how to verify if the range contained merged cells. Neither one of them seemed to be of interest.

 

[Joe4]

In looking at the original image, it looks like cells A1:C1 and Q1:R1 might be merged.

This is a good example of why merged cells are so bad! They cause all sorts of issues for things like this, sorting, and VBA, just to name a few.
Merged cells are so problematic that most programmers try to avoid them whenever possible.

If you are simply merged columns across single rows, you can use the "Center Across Selection" formatting option instead, which gives you the same visual effect as merged cells, but without all the issues.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis