Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Identify the last used cell before pasting

Posted on 2013-01-12
10
Medium Priority
?
218 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

618 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