VBA to fill down 10 rows

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
62
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
 
Last edited:

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
Hey,

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

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
62
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,502
Office Version
2010
Platform
Windows
This one-liner macro will find the last row and extend it by 10 rows keeping all formatting...
Code:
Sub ExtendByTenRows()
  Cells(Rows.Count, "A").End(xlUp).Resize(11).EntireRow.FillDown
End Sub
 
Last edited:

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
62
This one-liner macro will find the last row and extend it by 10 rows keeping all formatting...
Code:
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
 

Forum statistics

Threads
1,081,578
Messages
5,359,736
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top