Autofill range

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
Hello,

I have some data set up in a table like fashion. (not a table) B7:L17.

I am working on a macro to insert a row at the bottom, format as above, and the fill the formulas down.

Code:
Sub a()
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.AutoFill Destination:=[COLOR=red]Range(("B" & Range("B" & Rows.Count).End(xlUp).row) & ":L" & Range("B" & Rows.Count).End(xlUp).row + 1)[/COLOR], Type:=xlFillDefault
End Sub

the line in red give the problem. If I use range ("b7:b18") it is ok however since i would like to do this dynamic, that would not fill the new last line with the formulas above.

I won't be back till tonight. Thanks to all here.
Any insight is valued.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
I think the problem is you have the whole row highlighted and not columns B:L selected when you try to run the autofill.
Try this (note that I used a variable to "capture" the last row so I didn't have to keep re-calculating it):
Code:
Sub a()
 
    Dim myLastRow As Long
    myLastRow = Range("B" & Rows.Count).End(xlUp).Row
    Rows(myLastRow + 1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B" & myLastRow & ":L" & myLastRow).AutoFill Destination:=Range("B" & myLastRow & ":L" & myLastRow + 1), Type:=xlFillDefault
 
End Sub
 
Upvote 0
Joe4,

I would like to thank you for the code and especially for your thoughts and notes that you have provided. Very helpful.

As I am a VBA beginner (due to this website sparking my interest), I am very greatful for the 'fountain of knowledge' that is here.

Thanks again for taking the time to teach me something.
 
Upvote 0
Glad to help!
I love teaching people who want to learn!
 
Upvote 0

Forum statistics

Threads
1,226,737
Messages
6,192,736
Members
453,752
Latest member
Austin2222

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
Back
Top