0

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
Community
  • 1
  • 1
CustomX
  • 9,948
  • 30
  • 85
  • 115

3 Answers3

2

This should work:

a1 = Range("a1").Address
lastcell = Range("E1").End(xlDown).Address
Range(a1, lastcell).Copy
paulmorriss
  • 2,579
  • 25
  • 30
  • So it does. I've now fixed it so it does up to column E as the question said. – paulmorriss Mar 17 '11 at 09:02
  • Doesn't this only copy A1-B1...-D1-E1:E48? In my case? – CustomX Mar 17 '11 at 10:06
  • It copies everything in the rectangle from A1 to E48 in your case. If the last item in column E is E48, yet the other columns have data below row 48 then it won't copy those. Jean-François Corbett's comments about copying are worth paying attention to too. – paulmorriss Mar 17 '11 at 10:22
  • Have another look at my post, I've tried to redefine it. This might be clearer. – CustomX Mar 17 '11 at 10:26
2

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.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
  • thanks, so how would you reccomend this in my case? So will it now go down from A1 to A5, or A1 downwards to E1 downwards? And this will only copy cells where there's data? What do you mean by Avoid Copy/Paste? Sheets("1").Range("B1").Copy Destination:=Sheets("2").Range("A1").value Something like that? – CustomX Mar 17 '11 at 09:53
  • This also copies blancs, is possible to only select the fields that contain data? – CustomX Mar 17 '11 at 10:12
  • So many questions... The way I use the Range property above is Range(UpperLeftCell,LowerRightCell). The entire "rectangle" defined by these upper-left and lower-right corners will be selected. – Jean-François Corbett Mar 17 '11 at 10:19
  • "> What do you mean by Avoid Copy/Paste?" I gave an example in my answer... Do this, not this... Is it unclear in any way?... – Jean-François Corbett Mar 17 '11 at 10:20
  • I'll redefine my question above. – CustomX Mar 17 '11 at 10:21
  • "This also copies blancs, is possible to only select the fields that contain data?" It is possible, you'll have to use but another approach, too long to describe in a comment. I suggest you ask a new question with a detailed description of what you are trying to do. – Jean-François Corbett Mar 17 '11 at 10:30
  • http://stackoverflow.com/questions/5338725/excel-macro-copy-a-range-of-cells-and-only-select-cells-with-data – CustomX Mar 17 '11 at 12:32
0

If that suits you, my favorite is CurrentRegion: 

Range("A1").CurrentRegion.Copy Destination:=Sheet2.Range("a1")
iDevlop
  • 24,841
  • 11
  • 90
  • 149