My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Fill Down From Last Row Using Vba
|
|
 Apr 25 2012, 04:49 AM
Post
#1
|
|
|
UtterAccess Addict Posts: 231  |
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! |
 |
 
|
|
Apr 25 2012, 05:19 AM
Post
#2
|
|
|
UtterAccess VIP Posts: 13,031 From: Leicester, UK |
Can you post the code you are currently using?
|
|
|
|
|
Apr 25 2012, 06:19 AM
Post
#3
|
|
|
UtterAccess Addict Posts: 231 |
Cells(Application.Rows.Count, 1).End(xlUp).Offset(0, 0).Select
Selection.AutoFill Destination:=Range("A18:L19"), Type:=xlFillDefault |
|
|
|
|
Apr 25 2012, 10:21 AM
Post
#4
|
|
|
UtterAccess Certified! Posts: 6,927 From: Arizona, United States |
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: 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 |
|
|
|
|
Apr 25 2012, 10:24 AM
Post
#5
|
|
|
UtterAccess Certified! Posts: 6,927 From: Arizona, United States |
Sorry, i had a mistake.
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 |
|
|
|
|
Apr 25 2012, 01:11 PM
Post
#6
|
|
|
UtterAccess Addict Posts: 231 |
Thanks for that. I will give it a try tomorrow and let you know how I get on!
|
|
|
|
|
Apr 25 2012, 01:38 PM
Post
#7
|
|
|
UtterAccess VIP Posts: 4,295 |
Try this.
CODE Set rng = Range("A" & Rows.Count).End(xlUp).Resize(, 12)
rng.Copy Rng.Offset(1) |
|
|
|
|
Apr 25 2012, 01:39 PM
Post
#8
|
|
|
UtterAccess Certified! Posts: 6,927 From: Arizona, United States |
Norie,
I don't think that will auto fill a series.. |
|
|
|
|
Apr 25 2012, 01:48 PM
Post
#9
|
|
|
UtterAccess VIP Posts: 4,295 |
Is the OP filling a series?
Not sure they are, in the code they posted they used Type:=xlFillDefault, not xlFillSeries. |
|
|
|
|
Apr 25 2012, 01:49 PM
Post
#10
|
|
|
UtterAccess Certified! Posts: 6,927 From: Arizona, United States |
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. This post has been edited by ipisors: Apr 25 2012, 01:49 PM |
|
|
|
|
Apr 25 2012, 01:53 PM
Post
#11
|
|
|
UtterAccess VIP Posts: 8,167 From: Pacific NorthWet |
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! |
|
|
|
|
Apr 25 2012, 02:05 PM
Post
#12
|
|
|
UtterAccess VIP Posts: 4,295 |
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 |
|
|
|
|
Apr 25 2012, 02:19 PM
Post
#13
|
|
|
UtterAccess Certified! Posts: 6,927 From: Arizona, United States |
Jeff, I think you are misunderstanding ... I'm not the OP. (IMG:style_emoticons/default/smile.gif) 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. |
|
|
|
|
Apr 26 2012, 03:28 AM
Post
#14
|
|
|
UtterAccess Addict Posts: 231 |
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! |
|
|
|
|
Apr 26 2012, 09:48 AM
Post
#15
|
|
|
UtterAccess Certified! Posts: 6,927 From: Arizona, United States |
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. This post has been edited by ipisors: Apr 26 2012, 09:48 AM |
|
|
|
|
Apr 26 2012, 10:30 AM
Post
#16
|
|
|
UtterAccess VIP Posts: 4,295 |
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. |
|
|
|
|
Apr 27 2012, 03:48 AM
Post
#17
|
|
|
UtterAccess Addict Posts: 231 |
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.
|
|
|
|
|
Apr 27 2012, 07:05 AM
Post
#18
|
|
|
UtterAccess VIP Posts: 4,295 |
How would that be the same as a no that's incremented?
|
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 21st May 2013 - 03:37 AM |