PDA

View Full Version : Solved: Copy last row



debauch
04-13-2006, 07:43 AM
Hello,
How would I got about copying the last row in a worksheet?

Here is what I am working with :


Range("A2").Select
Selection.End(xlDown).Select
'Rows("12:12").Select
Application.CutCopyMode = False
'***************************
Selection.Copy


The row that is commented out, I would like it to grab the last row, rather than just row '12'. Any suggestions?

mvidas
04-13-2006, 08:40 AM
Hi Debauch,

Take a look at http://vbaexpress.com/kb/getarticle.php?kb_id=417
Using the xlLastRow function, you would only have to do something like:If xlLastRow > 0 Then
Rows(xlLastRow).Copy
End If

Ken Puls
04-13-2006, 08:47 AM
Or...


With Activesheet
.cells(.rows.count,1).end(xlup).entirerow.copy
end with

I think that should do it. Written in the browser and not tested though. :)

HTH,

mvidas
04-13-2006, 08:53 AM
That would work Ken, if column A determines the last row. You wouldn't even need the With ActiveSheet part, though I'm sure you added that in there so debauch could specify the sheet easier :)

Ken Puls
04-13-2006, 08:57 AM
Good point on column A, Matt. I should have mentioned that, yes.

With regards to Activesheet, I use it for two reasons...
1) I like to be explicit. I have been burned in the past with a range working on one sheet when I thought it was supposed to be on a different sheet. Since then, I've always explicitly stated it.
2) It makes the shorthand easier on the .rows.count within the .cells part. (I'm lazy) :)

It does have the added bonus of making it easier to change though, but honestly that's just a bonus to me. ;)

debauch
04-13-2006, 09:03 AM
Thanks. Both responses work equally the same. It seems to only be copying the first cell though, rather than the whole row. I tried a few things, but each time I change it , I end up sending the whole book.

mvidas
04-13-2006, 09:06 AM
It should definately copy more than just the first cell! Do you mind posting your whole code here, so we can examine it?

Ken, assuming you're working with the activesheet, what would be the difference:With Activesheet
.cells(.rows.count,1).end(xlup).entirerow.copy
End With
'versus
cells(rows.count,1).end(xlup).entirerow.copy

Ken Puls
04-13-2006, 09:11 AM
Matt, can't see that there should be a difference. Truly it comes down to if you do something dumb, like change the active sheet, when you're in the middle of the routine. Most likely, that's what I did. ;)

I just got in the habit of explicitly declaring the sheets. Maybe I'm just over cautious, but it's been working for me. I'm not sure if there is any performance gain/loss from it or not, but even if so, it would be minimal.

Just my style, I guess. :)

Oh.. and Debauch, please do post your routine. Something doesn't sound quite right...

mvidas
04-13-2006, 09:14 AM
Ken, if that is your style then keep it that way! You're being safer using the With block like that. I just usually use a worksheet variable for the same thing, as With blocks add 2 more lines to my code :)

Ken Puls
04-13-2006, 09:21 AM
LOL!

Dim wsSomething as Worksheet
Set wsSomething = Worksheets("Sheet1")

;)

debauch
04-13-2006, 10:01 AM
Got it, thanks guys . Please mark thread as solved.

instead of .copy , I just used select to highligh, and then the copy. Thanks again.

Ken Puls
04-13-2006, 10:05 AM
Hi debauch,

Glad you got it sorted, but that should be unnecessary. Personally, the first thing I do in any piece of recorded code is eliminate any select --> selection.method type things.

If you're okay with it, we can leave it, but I'm still kind of curious to see what you are using. :)

Zack Barresse
04-15-2006, 02:08 PM
The link MATT VIDAS posted is far more versatile than using .end(xlup). :)

Edit: Had to get the name right... :D

debauch
04-17-2006, 09:15 AM
Ken,
This is what I ended up with.


Sheets("Active").Select
With ActiveSheet
.Cells(.Rows.Count, 1).End(xlUp).EntireRow.Select
.Cells(.Rows.Count, 1).End(xlUp).EntireRow.Copy
End With


It is used to select the last entry, and email it to a group via Lotus. If their is a cleaner way I certainly welcome it. This is what has worked so far though.

mdmackillop
04-17-2006, 01:24 PM
A single line should accomplish this

Sheets("Active").Cells(Rows.Count, 1).End(xlUp).EntireRow.Copy

debauch
04-18-2006, 04:35 AM
Perfect. Tested, and verified =>

Ken Puls
04-19-2006, 10:13 AM
Sorry debauch,

Took the weekend off. Glad MD got you sorted. :)

debauch
04-19-2006, 10:48 AM
No worries. // I must say, not only is the layout of this site the best I've seen for vba, but the most helpful, and friendliest. I promise as I progress, I will contribute some KB's or answers rather than just questions.

Ken Puls
04-19-2006, 10:56 AM
I must say, not only is the layout of this site the best I've seen for vba, but the most helpful, and friendliest. I promise as I progress, I will contribute some KB's or answers rather than just questions.

Cool! Thanks for the feedback, and we look forward to your contributions. :thumb

Best way to keep learning, too, IMHO. ;)