On my spreadsheet I'm trying to create a macro that will select the last row of data in my spreadsheet (A18:L18) and fill down to the next row. The problem I have is after performing this once the range A18:L18 is no longer the last row in the spreadsheet (now is A19:L19).
In summary I guess I am trying to look for some code which will select the last row of data and fill down to the row below (so that data in last row increases).
I have tried playing around with .offset but couldn't get anything to work.
Any pointers in the right direction greatly appreciated!
Full Version: Fill Down From Last Row Using Vba
Can you post the code you are currently using?
Cells(Application.Rows.Count, 1).End(xlUp).Offset(0, 0).Select
Selection.AutoFill Destination:=Range("A18:L19"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("A18:L19"), Type:=xlFillDefault
I use AutoFill in VBA a lot. Usually just for one column, but you can tweak this to make it work.
One thing that's important to remember is that Autofill method must include the object that's initiating it. Example, if myRange is a Range variable which you've Set to A19, then the AutoFill statement MUST include A19 itself.
May I also recommend completely qualifying things and not using SELECT in any place in your code or depending on it.
Try this, aircode, copy / paste in module, make adjustments as necessary:
One thing that's important to remember is that Autofill method must include the object that's initiating it. Example, if myRange is a Range variable which you've Set to A19, then the AutoFill statement MUST include A19 itself.
May I also recommend completely qualifying things and not using SELECT in any place in your code or depending on it.
Try this, aircode, copy / paste in module, make adjustments as necessary:
CODE
dim lastrow as integer
dim nextrow as integer
dim ws as worksheet
set ws = thisworkbook.worksheets("Sheet1") 'adjust to suit
lastrow=ws.range("A" & ws.rows.count).end(xlup).row 'adjust "A" to be whichever column you're sure won't be blank...
nextrow=lastrow+1
ws.Range("A" & lastrow).AutoFill Destination:=ws.Range("A" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("B" & lastrow).AutoFill Destination:=ws.Range("B" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("C" & lastrow).AutoFill Destination:=ws.Range("C" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("D" & lastrow).AutoFill Destination:=ws.Range("D" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("E" & lastrow).AutoFill Destination:=ws.Range("E" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("F" & lastrow).AutoFill Destination:=ws.Range("F" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("G" & lastrow).AutoFill Destination:=ws.Range("G" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("H" & lastrow).AutoFill Destination:=ws.Range("H" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("I" & lastrow).AutoFill Destination:=ws.Range("I" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("J" & lastrow).AutoFill Destination:=ws.Range("J" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("K" & lastrow).AutoFill Destination:=ws.Range("K" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("L" & lastrow).AutoFill Destination:=ws.Range("L" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
dim nextrow as integer
dim ws as worksheet
set ws = thisworkbook.worksheets("Sheet1") 'adjust to suit
lastrow=ws.range("A" & ws.rows.count).end(xlup).row 'adjust "A" to be whichever column you're sure won't be blank...
nextrow=lastrow+1
ws.Range("A" & lastrow).AutoFill Destination:=ws.Range("A" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("B" & lastrow).AutoFill Destination:=ws.Range("B" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("C" & lastrow).AutoFill Destination:=ws.Range("C" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("D" & lastrow).AutoFill Destination:=ws.Range("D" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("E" & lastrow).AutoFill Destination:=ws.Range("E" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("F" & lastrow).AutoFill Destination:=ws.Range("F" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("G" & lastrow).AutoFill Destination:=ws.Range("G" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("H" & lastrow).AutoFill Destination:=ws.Range("H" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("I" & lastrow).AutoFill Destination:=ws.Range("I" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("J" & lastrow).AutoFill Destination:=ws.Range("J" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("K" & lastrow).AutoFill Destination:=ws.Range("K" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("L" & lastrow).AutoFill Destination:=ws.Range("L" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
Sorry, i had a mistake.
Sub Test()
Dim lastrow As Integer
Dim nextrow As Integer
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1") 'adjust to suit
lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row 'adjust "A" to be whichever column you're sure won't be blank...
nextrow = lastrow + 1
ws.Range("A" & lastrow).AutoFill Destination:=ws.Range("A" & lastrow & ":" & "A" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("B" & lastrow).AutoFill Destination:=ws.Range("B" & lastrow & ":" & "B" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("C" & lastrow).AutoFill Destination:=ws.Range("C" & lastrow & ":" & "C" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("D" & lastrow).AutoFill Destination:=ws.Range("D" & lastrow & ":" & "D" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("E" & lastrow).AutoFill Destination:=ws.Range("E" & lastrow & ":" & "E" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("F" & lastrow).AutoFill Destination:=ws.Range("F" & lastrow & ":" & "F" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("G" & lastrow).AutoFill Destination:=ws.Range("G" & lastrow & ":" & "G" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("H" & lastrow).AutoFill Destination:=ws.Range("H" & lastrow & ":" & "H" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("I" & lastrow).AutoFill Destination:=ws.Range("I" & lastrow & ":" & "I" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("J" & lastrow).AutoFill Destination:=ws.Range("J" & lastrow & ":" & "J" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("K" & lastrow).AutoFill Destination:=ws.Range("K" & lastrow & ":" & "K" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("L" & lastrow).AutoFill Destination:=ws.Range("L" & lastrow & ":" & "L" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
End Sub
CODE
Sub Test()
Dim lastrow As Integer
Dim nextrow As Integer
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1") 'adjust to suit
lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row 'adjust "A" to be whichever column you're sure won't be blank...
nextrow = lastrow + 1
ws.Range("A" & lastrow).AutoFill Destination:=ws.Range("A" & lastrow & ":" & "A" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("B" & lastrow).AutoFill Destination:=ws.Range("B" & lastrow & ":" & "B" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("C" & lastrow).AutoFill Destination:=ws.Range("C" & lastrow & ":" & "C" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("D" & lastrow).AutoFill Destination:=ws.Range("D" & lastrow & ":" & "D" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("E" & lastrow).AutoFill Destination:=ws.Range("E" & lastrow & ":" & "E" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("F" & lastrow).AutoFill Destination:=ws.Range("F" & lastrow & ":" & "F" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("G" & lastrow).AutoFill Destination:=ws.Range("G" & lastrow & ":" & "G" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("H" & lastrow).AutoFill Destination:=ws.Range("H" & lastrow & ":" & "H" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("I" & lastrow).AutoFill Destination:=ws.Range("I" & lastrow & ":" & "I" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("J" & lastrow).AutoFill Destination:=ws.Range("J" & lastrow & ":" & "J" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("K" & lastrow).AutoFill Destination:=ws.Range("K" & lastrow & ":" & "K" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
ws.Range("L" & lastrow).AutoFill Destination:=ws.Range("L" & lastrow & ":" & "L" & nextrow), Type:=xlFillSeries 'unless you want xlFillCopy, I'm not sure
End Sub
Thanks for that. I will give it a try tomorrow and let you know how I get on!
Try this.
CODE
Set rng = Range("A" & Rows.Count).End(xlUp).Resize(, 12)
rng.Copy Rng.Offset(1)
rng.Copy Rng.Offset(1)
Norie,
I don't think that will auto fill a series..
I don't think that will auto fill a series..
Is the OP filling a series?
Not sure they are, in the code they posted they used Type:=xlFillDefault, not xlFillSeries.
Not sure they are, in the code they posted they used Type:=xlFillDefault, not xlFillSeries.
Good point, I guess. if they are filling a series then specify it using my auto fill method.
from their mention "so that the data in the last row increases", kind of made me think they were not just copying.
but yeah, if they want to just copy - your method is much better (cuz simpler). Thanks for the assistance.
from their mention "so that the data in the last row increases", kind of made me think they were not just copying.
but yeah, if they want to just copy - your method is much better (cuz simpler). Thanks for the assistance.
Isaac
I may be misinterpreting your description to mean that you want to have the cursor travel down until it gets to the last cell with a value, then move down one more, in preparation for data entry (i.e., "in a new row").
If I were doing that manually, I'd use the Ctrl-DownArrow to get to the 'boundary'.
... or maybe I am just misinterpreting!
I may be misinterpreting your description to mean that you want to have the cursor travel down until it gets to the last cell with a value, then move down one more, in preparation for data entry (i.e., "in a new row").
If I were doing that manually, I'd use the Ctrl-DownArrow to get to the 'boundary'.
... or maybe I am just misinterpreting!
If they do want to fill a series it could perhaps be done with much the same code as I posted.
CODE
Set Rng = Range("A" & Rows.Count).End(xlUp).Resize(, 12)
Rng.AutoFill Rng.Resize(2), xlFillSeries
Rng.AutoFill Rng.Resize(2), xlFillSeries
Jeff, I think you are misunderstanding ... I'm not the OP. 
I just posted some code as a solution for them.
They want to use Excel's AUTOFILL method. My code works, but I confess Norie's is simpler, if the second version of what Norie posted will work for auto fill as well.
Either way, good opp. for OP to learn vba autofill. I find it super handy.
I just posted some code as a solution for them.They want to use Excel's AUTOFILL method. My code works, but I confess Norie's is simpler, if the second version of what Norie posted will work for auto fill as well.
Either way, good opp. for OP to learn vba autofill. I find it super handy.
Both methods worked great thanks!
is there a setting in the autofill by where I can get it to only fill in the numbers? I.e I'd like the text in one of the columns to fill down like so...
m200x1.ab
m200x2.ab
m200x3.ab
etc etc.
thanks!
is there a setting in the autofill by where I can get it to only fill in the numbers? I.e I'd like the text in one of the columns to fill down like so...
m200x1.ab
m200x2.ab
m200x3.ab
etc etc.
thanks!
You mean you want Excel to recognize a series as that incrementing 1, 2 or 3 ? If the 1 , 2, 3 is repeating, then autofill may recognize it as a series. At what point Excel recognizes a non-obvious pattern as a series and autofills based on that series is not something I have ever seen completely defined. In fact I'm not sure it could be defined, since there is no limit except imaging for what a user could consider a 'series'.
I really don't think the autofill method will work for that. I could be wrong, maybe Norie has an idea. Other than just completely re-coding it, of course, to add a number there. Which woudl be a totally diff. problem + solution.
I really don't think the autofill method will work for that. I could be wrong, maybe Norie has an idea. Other than just completely re-coding it, of course, to add a number there. Which woudl be a totally diff. problem + solution.
If the number was at the end you might be able to use AutoFill.
Since it's in the middle I can't see it being done without a formula.
Since it's in the middle I can't see it being done without a formula.
As an alternative then, is there code I could run that would place ".ea" at the end of each of those cells? using a blank cell with =A1&".ea" wouldn't really suit my needs.
How would that be the same as a no that's incremented?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.