Copy until the last piece of data

Question

Is it possible to select a range to copy to the last item?

Sheets("Design").Select
Range("A1:C200").Copy

'My data only runs until E48 and Im left with 152 blancs.

So now it copies from A1 to E200, but how can I edit the above code so it only selects until the last piece of data, which in my case is E48? (this is variable)

Thanks!

@Jean

In my excel sheet I have data running from A1-A18, B is empty and C1-C2. Now I would like to copy all the cells that contain a value.

 With Range("A1")
     Range(.Cells(1, 1), .End(xlDown).Cells(20, 3)).Copy
 End With

This will copy everything from A1-C20, but I only want A1-A18 and C1-C2 seen as though these contain data. But it needs to be formed in a way that once I have data in B or my range extends, that these get copied too.

Is this perhaps a bit clearer?

About the Copy/Paste situation;

I currently paste using this

appWD.Selection.PasteSpecial ' So it copies the formats too?
'In your case, do you mean I should do it like this?
Range(.Cells(1, 1), .End(xlDown).Cells(20, 3)).Copy Destination:=appWD.Selection.PasteSpecial

Answer 1

This works:

With Range("A1")
    Range(.Cells(1, 1), .End(xlDown).Cells(1, 5)).Copy
End With

Change the "5" if you need a different number of columns.

Also, this I learned the hard way: Avoid Copy/Paste if at all possible! Copy and Paste use the clipboard. Other programs may read from / write to the clipboard while your code is running, which will cause wild, unpredictable results. However, it's safe to copy to your target range on the same line, i.e. do this

Range(.Cells(1, 1), .End(xlDown).Cells(1, 5)).Copy myDestinationRange

and not this

Range(.Cells(1, 1), .End(xlDown).Cells(1, 5)).Copy
myDestinationRange.Paste

The latter uses the clipboard while the former does not.

Answer 2

If that suits you, my favorite is CurrentRegion:

Range("A1").CurrentRegion.Copy Destination:=Sheet2.Range("a1")

Answer 3

This should work:

a1 = Range("a1").Address
lastcell = Range("E1").End(xlDown).Address
Range(a1, lastcell).Copy