VBA Autofill hanging - quick debug

jardenp

Active Member
Joined
May 12, 2009
Messages
369
My macro code is doing a slow hang when I try to extend the formulas in I2 and J2 down through however many rows there are. It only does this when row 2 is the last row, i.e., when there is only 1 row of data below the header labels. By "slow hang" I mean the progress bar pops up, you can tell it's working through something. Then the values in I2 and J2 appear briefly in each cell in columns I and J and then disappear. Here's my code:

Code:
 'Format fields - Change Product code, Quantity, and Pump# for copied rows
        'Product
    Range("I2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=IF(RC[-8]>0,2,"""")"
        
        'Quantity
    Range("J2").Select
        ActiveCell.FormulaR1C1 = "=IF(RC[-9]>0,RC[17],"""")"
    Range("I2:J2").Select
        Selection.AutoFill Destination:=Range("I2:J" & Range("A2").End(xlDown).Row), Type:=xlFillDefault

I use "Range("XX").End(xlDown).Row" all the time to find the last row and I've never had problems before. Is this a technique that is not suited to instances where you can't extend--where the row in the code (here row 2) is the final row?

Thanks!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

ZAX

Well-known Member
Joined
Jul 5, 2012
Messages
715
Try using this:
Code:
 'Format fields - Change Product code, Quantity, and Pump# for copied rows
        'Product
    Range("I2").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=IF(RC[-8]>0,2,"""")"
        
        'Quantity
    Range("J2").Select
        ActiveCell.FormulaR1C1 = "=IF(RC[-9]>0,RC[17],"""")"
    Range("I2:J2").Select
        Selection.AutoFill Destination:=Range("I2:J" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
NOTE:If that doesn't work, try removing ", Type:=xlFillDefault"
ZAX
 

Watch MrExcel Video

Forum statistics

Threads
1,133,531
Messages
5,659,360
Members
418,499
Latest member
mbcmel

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