[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

Identify the last used cell before pasting

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
gisvpn
Asked:
gisvpn
  • 5
  • 5
1 Solution
 
SteveCommented:
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
 
gisvpnAuthor Commented:
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
 
SteveCommented:
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
Independent Software Vendors: 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!

 
gisvpnAuthor Commented:
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
 
SteveCommented:
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
 
gisvpnAuthor Commented:
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
 
SteveCommented:
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
 
gisvpnAuthor Commented:
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
 
gisvpnAuthor Commented:
Thanks for the comments in the code.
0
 
SteveCommented:
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now