?
Solved

Identify the last used cell before pasting

Posted on 2013-01-12
10
Medium Priority
?
216 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

752 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