Solved

Identify the last used cell before pasting

Posted on 2013-01-12
10
212 Views
Last Modified: 2013-01-12
Hello,

I have a VBA project that copies cells from another spreadsheet into the current spreadsheet:

Sheets("sheet1").Select
Columns("A:A").Select
ActiveSheet.Paste

I would like to amend this slightly because sometimes these are values in the A column already that I would not want to overwrite.

Therefore I would like to only paste the results from the row immediately after the last just row in the A column – how could I do this with VBA?

Many thanks,

GISVPN
0
Comment
Question by:gisvpn
  • 5
  • 5
10 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38769867
Could you provide a tad more of the code (where the data comes from) as is is not good practice to select in the way you do there... you should just use the .copy (as exampled below)

Just a simple example which copies from one range to another...
Workbooks("Workbook1).Sheets("Sheet1").Range("A1:A" & LastDataRow1).copy Workbooks("Workbook2).Sheets("Sheet1").Range("A" & LastDataRow2 & ":A" & LastDataRow1 + LastDataRow2)

Open in new window


lastDataRow 1 and 2 are Determined using a formula like:

with Workbooks("Workbook1).Sheets("Sheet1")
    LastDataRow1 = .Cells(.Rows.Count, "A").Row
end with

Open in new window

0
 

Author Comment

by:gisvpn
ID: 38769881
Hi The_Barman!.

thanks for the reply. Here is the full section of code -would you recommend the same approach?



Sheets("Data").Select
Columns("F:F").Select
Selection.Copy
Sheets("sheet1").Select
Columns("A:A").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$A$50000").RemoveDuplicates Columns:=1, Header:=xlYes

Open in new window

0
 
LVL 24

Expert Comment

by:Steve
ID: 38769891
The following one line will do exactly the same as lines 1 to 7 from your code...
Sheets("Data").Range("F:F").copy Sheets("Sheet1").Range("A:A")

Open in new window


If you wish to tag on to the end of the last row of data:
The following may do it...

with Sheets("Data")
    LastDataRow1 = .Cells(.Rows.Count, "F").Row
end with

with Sheets("Sheet1")
    LastDataRow2 = .Cells(.Rows.Count, "A").Row
end with

Sheets("Data").Range("F2:F" & LastDataRow1).copy Sheets("Sheet1").Range("A" & LastDataRow2 + 1 & ":A" & LastDataRow1 + LastDataRow2)

Open in new window

0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:gisvpn
ID: 38769899
Thanks! That makes it a little more tidy!!

Would I add in the line of code to ensure I go to the last used row on Range (A:A) just before this line if I were to use this one line?
0
 
LVL 24

Expert Comment

by:Steve
ID: 38769900
to add to the end... this is the one (testing as I type) :)

Sub Names_of_worksheets()


'find last row of data on sheet "data" in column "F"
With Sheets("Data")
    LastDataRow1 = .Cells(.Rows.Count, "F").End(xlUp).Row
End With

'find last row of data on sheet "sheet1" in column "A"
With Sheets("Sheet1")
    LastDataRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'Copy "Data" in column F from row 2 to the last row of data
'paste it to the row one row down from the last row in sheet1
Sheets("Data").Range("F2:F" & LastDataRow1).Copy Sheets("Sheet1").Range("A" & LastDataRow2 + 1 & ":A" & LastDataRow1 + LastDataRow2)

End Sub

Open in new window


This assumes a header row in column F :) so exclude row 1 (start F at row 2)

In Excel 2010 it is more important to implicitly define the size of ranges...
As an xls file only had 65k of rows selecting and copying entire columns was no biggie.
But if you do this with the new 10m row limit of xlsm files you will have memory issues.
0
 

Author Comment

by:gisvpn
ID: 38769914
not sure it works.... I have this at the moment - works great.

Sheets("Data").Range("F:F").Copy Sheets("sheet1").Range("A:A")

Sheets("sheet1").Select

ActiveSheet.Range("$A$1:$A$50000").RemoveDuplicates Columns:=1, Header:=xlYes

The only thing I would like to do is start  the copy of 'Sheets("sheet1").Range("A:A")' from next row not used in the A column ;)
0
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 38769921
The single line above was a "copy" of your original code (just to show how to replace the 7 lines with one)... the following code (from above) does the paste below the existing data as requested...

Sub Test()

'find last row of data on sheet "data" in column "F"
With Sheets("Data")
    LastDataRow1 = .Cells(.Rows.Count, "F").End(xlUp).Row
End With

'find last row of data on sheet "sheet1" in column "A"
With Sheets("Sheet1")
    LastDataRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'Copy "Data" in column F from row 2 to the last row of data
'paste it to the row one row down from the last row in sheet1
Sheets("Data").Range("F2:F" & LastDataRow1).Copy Sheets("Sheet1").Range("A" & LastDataRow2 + 1 & ":A" & LastDataRow1 + LastDataRow2)

End Sub

Open in new window


and try to avoid selecting (it is slow to excecute)

delete any .select  activesheet

e.g
Sheets("sheet1").Select

ActiveSheet.Range("$A$1:$A$50000").RemoveDuplicates Columns:=1, Header:=xlYes

Open in new window

becomes:
Sheets("sheet1").Range("$A$1:$A$50000").RemoveDuplicates Columns:=1, Header:=xlYes

Open in new window

0
 

Author Comment

by:gisvpn
ID: 38769930
Perfect thanks for the comments they really help me understand it.

I had to keep the Sheets("sheet1").Select for the duplicate (the code that follows that) to work. ;)

Works well now ;)
0
 

Author Closing Comment

by:gisvpn
ID: 38769933
Thanks for the comments in the code.
0
 
LVL 24

Expert Comment

by:Steve
ID: 38769937
I had added a little bit to the end of my last comment about the sheet.select.

It is best to avoid that as much as possible.
The bigest cause of slow macro code I have experienced is the overuse of select statements.
They are almost always avoidable. And always have a huge impact on runtimes.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now