PDA

View Full Version : Selecting more than cell VBA



MarkNumskull
08-13-2009, 11:34 AM
Hi,

Fairly simple question but i cant find an answer anywhere. I want to select two adjacent cells in a worksheet, copy, then offset down to the next row until there are no values left ie

copy D16:E16 then offset down to D17:E17 and copy until no values

Does anyone know a way to do this. The result is then pasted into sheet 3.

I have it all sorted but it is just how to select two cells, copy and then offset it so that the two cells below are selected that im having trouble with.

Thanks in advance

Mark

mdmackillop
08-13-2009, 11:39 AM
I'm not totally clear what you are after, but to extend a range you can use Resize
Maybe

Range("D16:E16").Resize(1).Copy

MarkNumskull
08-13-2009, 11:53 AM
Apologies,

I have pasted an example sheet in here, as you will see range A1:B1 to A3:B3 have values - what i want to do is only select the ranges in the a&b columns with values and then paste into sheet 3. the reason i cant just do range(A1:B3).copy is because each time there there will be different amounts of data in the colums, one time there could be 1 row next there could be 10.

mdmackillop
08-13-2009, 12:01 PM
Some choices depending upon your data

Sub Ranges()
Range("A1").CurrentRegion.Copy
'or
Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Resize(, 2).Copy
'or
Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)).Resize(, 2).Copy
End Sub

MarkNumskull
08-13-2009, 12:32 PM
Hi!

Thanks for the help, you have taught me something new, iv never heard of the .resize function before so i got mine to work like so

Do Until ActiveCell.Value = 0
ActiveCell.Resize(, 2).Copy
ActiveCell.Offset(1, 0).Select

obv there is more code but it selects and copies both ranges together and pastes them in then repeats until there are no more values. i need this as i need the date beside each line rather than pasting in bulk.

Many Thanks!

mdmackillop
08-13-2009, 02:35 PM
Try to avoid Selecting. It slows down execution.
Use something like

With ActiveCell
Do
.Offset(i).Resize(, 2).Copy Cells(i + 1, 5)
i = i + 1
Loop Until .Offset(i) = 0
End With

MarkNumskull
08-13-2009, 02:43 PM
Oh does it? I didn't know that, im sure my code looks to you like what an air plane made out of wood and fabric would look like to an aeronautical engineer (clunky and awful!). I will see if i can figure how to put that into my sub to get it speed up a wee bit. Thanks for the input!

Mark

mdmackillop
08-13-2009, 02:46 PM
If you post your workbook, we can look at the best way. I suspect the looping is not required.