Copy data upto last used Row of a specific column

Question

I am using this code to get the last used rows-

rowNo=objExcel.Activeworkbook.Sheets(1).UsedRange.Rows.count

Now, i want to copy the data of a specific column upto last row used.

//What to write in the below code to use rowNo //

Set Source=objExcel.Activeworkbook.Sheets(1).Column("G")

Source.Copy

score 1 · Answer 1 · edited May 23 '17 at 12:18

1

That's the incorrect way to find the last row. See the link on how to find the last row.

Once you have found the last row then simply use this code to copy

ws.Range("G1:G" & rowNo).Copy

Where ws is the worksheet object.

edited May 23 '17 at 12:18

Community

answered Feb 17 '13 at 04:01

Siddharth Rout

Thanks for the response! Just have one query- When i am pasting all the values in the excel , all values are getting overlapped on one-another.How can i make it to paste one by one... Set dest=objExcel2.Activeworkbook.Sheets(1).Columns("A") dest.PasteSpecial – Praveenks Feb 17 '13 at 04:41
Sorry I do not understand? Do you want to transpose the data from the column to a row? – Siddharth Rout Feb 17 '13 at 04:45
No, There are some excel files in a folder.I am trying to copy a specific column value and put it into a single excel sheet.What you had suggested with the help of that i am able to copy all the values but while pasting it into a new excel sheet.All these values are getting pasted over one-another.I want all these values to be pasted one by one( like if there are 12 values in the first sheet then it will be pasted after this second sheet values and so on..) – Praveenks Feb 17 '13 at 04:59
That is because you are pasting it in Col A directly. You have to find the last row in the destination sheet as well and then paste after that – Siddharth Rout Feb 17 '13 at 05:10

1 Answers1