Solved

Identify the last used cell before pasting

Posted on 2013-01-12
10
210 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:gisvpn
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:gisvpn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for the comments in the code.
0
 
LVL 24

Expert Comment

by:Steve
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

11 Experts available now in Live!

Get 1:1 Help Now