Autofill down to last PLUS additional row

[Cknight]

I have a data that I paste in column A that is dynamic in quantity when I paste it in.
Formulas are written into cells B2:BS2

I am familiar with the formula below to get the formulas to fill to the last row

LastRow = Range("A2").End(xlDown).Row
Range("B2:BS2").AutoFill Destination:=Range(Range("B2"), Range("BS" & LastRow))

I was wondering if there were a way to add something to that formula to fill one additonal row past the last row.

For example, I will add data to cells A2:A9. Is there any way to have the macro fill the cells B2:BS10, instead of just B2:BS9?

I sometimes have to paste additional data on occasion so in the same scenario, I would have A2:A9 already pasted and would then paste data into A10:A20. I would like the macro to fill from B10:BS21 in that instance.

If possible, the VBA would be able to handle both situations.

Thanks so much in advance for any help!

 

[chirayu]

Change the code from "LastRow))" to "LastRow + 1))"

Or just define the +1 from the start in your variable

 

[NARAYANK991]

Hi ,

I am not able to understand why you want the change.

The code that you have will always use the formula in row 2 to paste in the range B2:BSX where X is the last row as calculated using column A.

Thus , whether you have already formulae in columns B through BS is immaterial ; the auto fill will always fill till the last row of pasted data.

Copying the formula to an additional row is , in my opinion , not required.

Narayan

 

[Cknight]

Thanks for getting back to me, Chirayu and NARAYANK991!

That was certainly easy to add the + 1 to the VBA. That fixes my first issue immediately

For my second scenario, it does fill down from B2 where I was hoping for it to fill down from B10 to the last row plus one. Is there a way to reference the last row in column B and use that row to fill down to the last row in column A plus the additional row?

NARAYANK991, for some context on how I use this. It is an order sheet and I add 9 items that I order. I fill down the additional row because I may have to make some changes to the first 9 rows which would change or value out the formulas.

Filling an additional row keeps the original formulas unchanged when I have to add on an additional 20 items to the orders sheet in A10:A30 in case I change anything in row 9. Adding solely Last Row +1 to the VBA will paste over any changes I make in cells B2:BS9.

Row 1 is comprised of headers if that information is helpful.

 

[chirayu]

To reference last row use a variable e.g.

 Sub MyLastRow
Dim LstRow as Integer
LstRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2:C" & LstRow).Filldown

 

[Cknight]

chirayu-

Should I be adding that to the previous code of

LastRow = Range("A2").End(xlDown).Row
Range("B2:BS2").AutoFill Destination:=Range(Range("B2"), Range("BS" & LastRow + 1))

I tried to add it in and it just filled in column C down to the last row of formulas in row A.

I am trying to have the VBA
-Always find the last row in B with formulas
-Fill rows B:BS down from that last row in B to the last row of A with data plus one additional row

Is that even possible?

 

[chirayu]

To find Last Row of A:

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

To fill +1 row of B

Range("B2:BS2").AutoFill Destination:=Range("B2:BS" & LastRow + 1)

 

[NARAYANK991]

Hi ,

Use different variables as follows :

LastRow_A = Cells(Rows.Count, "A").End(xlUp).Row

LastRow_B = Cells(Rows.Count, "B").End(xlUp).Row

Range("B" & LastRow_B & ":" & "BS" & LastRow_B).AutoFill Destination:=Range("B" & LastRow_B & ":" & "BS" & LastRow_A + 1)

Narayan

 

[Cknight]

This is so close to working!

However, by changing the LastRow Code from
"LastRow = Range("A2").End(xlDown).Row" to
LastRow_A = Cells(Rows.Count, "A").End(xlUp).Row

it now picks up and fills down to some summary formulas I have in cells A2500:2796. There are always blank cells between the data I paste into column A and A2500 so the former LastRow code would stop when it found a blank cell.

Is there a way to omit anything below row 2500 or an easier way to not include the summary formulas in rows 2500:2796 ? I would only need the fill for B:BS for Rows 2 to 2500 before starting a new sheet

Thanks again! Sorry for so many variables!

 

[chirayu]

If you need to find last row above 2500 then change your variable to

LastRow = Range("A2500").End(xlUp).Row