Excel find End of Range then Select Range and Copy

Question

this may or may not have been asked yet.

I am trying to copy a Range of Cells from one worksheet to another using a macro. the problem I am having is that the Range I want to copy varies drastically every time the macro is run.

For example I wan to Copy Range("A2:P64") on first run, then when i run the data again the start of the Range is still A2, but the end of the range may be P25 or p90000. It is always Column P and (x variable) Row.

I have tried things like this:

Dim past As Worksheet
Dim countHist As String
Dim view As Worksheet
Dim countView As String

Set view = ThisWorkbook.Worksheets("BlueSteel Errors")
Set past = ThisWorkbook.Worksheets("Historical")

countView = view.Cells(Rows.Count, "A").End(xlUp).Row
countHist = past.Cells(Rows.Count, "A").End(xlUp).Row
MsgBox ("P" & countView) ' This currently gives the count of 53, so output is P53

view.Range("A2:P & countView").Select   ' THIS IS THE PROBLEM LINE.. should look
'should resemble view.Range("A2:P53").Select
'EVERYTHING ELSE APPEARS TO WORK

Selection.Copy
past.Select
Range("A" & countHist).Select
ActiveSheet.Paste

I cannot find a way to determine the last line that text exists, and then append that number to the end of the Range field so i can Select and append to the other sheet.. I only need the data from A2 - Px not All of the excess fields beyond P. Perhaps I need to copy then delete that.

It should be like this `view.Range("A2:P" & countView).Select`. — L42, Feb 07 '14 at 23:58

Shiva · Answer 1 · 2014-02-07T23:41:04.067

1

You need the End(xlUp) function.

So update your problematic line

from

view.Range("A2:P & countView").Select   ' THIS IS THE PROBLEM LINE..

to

view.Range("A2:" & view.Range("P" & Cells.Rows.Count).End(xlUp).Address).Select

edited Feb 07 '14 at 23:41

answered Feb 07 '14 at 23:25

Shiva

20,575
14
82
112

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

Please... Please...Please... Please...Please... Please...Please... Please...Please... Please...Please... Please...Please... Please...Please... Please...Please... Please... do not use .Select INTERESTING READ

Also there are couple of errors in your code

You have declared countHist and countView as String. It should be Long
Anything within quotes is a string. So "A2:P & countView" is a string. You need "A2:P" & countView
You need to add 1 to the last row of destination sheet so that you do not overwrite the last row.

Try this

Sub Sample()
    Dim past As Worksheet, view As Worksheet
    Dim countHist As Long, countView As Long

    Set view = ThisWorkbook.Worksheets("BlueSteel Errors")
    Set past = ThisWorkbook.Worksheets("Historical")

    countView = view.Cells(view.Rows.Count, "P").End(xlUp).Row
    countHist = past.Cells(past.Rows.Count, "A").End(xlUp).Row + 1

    '~~> Do a direct copy paste instead of selecting first
    view.Range("A2:P" & countView).Copy past.Range("A" & countHist)
End Sub

You may need to use .Find to find the last row is not all cells are filled up in a particular row. More about finding last row HERE

Thank you.. that is very close to what my original code looked like, and yes the Select feature has its downside.. Thank you for spotting my "Oversight... :D " This is reason I dont code for more than an hour or two at a time.. The code starts to look the same and always right. Even when its wrong. — MBNE08, Feb 10 '14 at 13:57

Excel find End of Range then Select Range and Copy

2 Answers2