VBA to fill down 10 rows

[Chewyhairball]

Ok guys I am finally giving up after trying to work this outfor most of the day.
I have a code that takes me to the last row of formulas I have copied down. It then selects that entire row.I would like it to be able to then fill down from this row to the 10 rows below.
I can manually drag the cells down and it copies everything down fine. The reason I want to fill/drag it down is it is also a table and has conditional formatting. If I drag it as opposed to copy and paste it extends the table which auto updates other formulas and the conditional formatting.
I can get as far as getting to the last used row and selecting it with:

Sub lastrow()
Dim lngLastRow As Long
Range("A10").End(xlDown).Select
lngLastRow =ActiveCell.Row
Rows(lngLastRow).Select
End Sub

But I cant find the equivalent code for then dragging this down another 10 rows.
It will be connected to a button on the spreadsheet so anytime anyone wants to add another 10 lines they just have to click it.
Any help would be greatly appreciated thanks


Rory

 

[tyija1995]

Hey,

You could set lngLastRow = ActiveCell.Offset(10, 0).Row instead if you want 10 rows from the last row

 

[Chewyhairball]

Hi
Thanks, but that won't help me. I need it to drag the cell down to copy it. That just moves me to the cell 10 rows away.
Rory

 

[Rick Rothstein]

This one-liner macro will find the last row and extend it by 10 rows keeping all formatting...

Sub ExtendByTenRows()
  Cells(Rows.Count, "A").End(xlUp).Resize(11).EntireRow.FillDown
End Sub

 

[Chewyhairball]

This one-liner macro will find the last row and extend it by 10 rows keeping all formatting...

Sub ExtendByTenRows()
  Cells(Rows.Count, "A").End(xlUp).Resize(11).EntireRow.FillDown
End Sub

Ah! Rick! thank you very much. I have spent many hours trying to work this out.
Your code is perfect for what I need. I can get some sleep now

thanks again

Rory