How select cells? 1

[garrbel]

Hi

We can select cells. I do like this:
Cells(i, 2).Select
Selection.Copy

Can I select cells (i,2) (i,3) (i,5) in one statement?(non adjacent cells)

cells (i,2) (i,3) (i,5). select
Selection.copy

thanks

 

[davedave24]

sheets("sheet1").range("I1:I5").select

 

[SkipVought]

hi,

Can I select cells (i,2) (i,3) (i,5) in one statement?(non adjacent cells)

Have you ACTUALLY tried doing that on your sheet?

Please TRY to do that on your sheet and you will discover something.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Application.Union(Range("I2:I3"), Range("I5")).Copy

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[garrbel]

davedave24
is not a all range I need

as you said is all range: i1,i2,i3,i4,i5

What I ask is for no adjacent cells like i1,i3,i5

Cells(i, 1).Select
Selection.Copy
Cells(i, 3).Select
Selection.Copy

Do I have to do like this or can I do in one statement?
Cells i1,i3,i5.......select
Selection.Copy

 

[PHV]

Again, have a look at the Application.Union method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

YOU could answer that question YOUSELF, by trying to COPY multiple, non-contiguous cells.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[garrbel]

I'm so sorry but I can't get the answer for this:

i = 9
While Cells(i, 1) <> ""
i = i + 1
Wend
i = i - 1
Rows(i).Select
Selection.Insert Shift:=xlDown

Application.Union(Range(i,2: i,3), Range(i, 5)).Copy

Can you point me for how to do?

 

[SkipVought]

You are hopeless!

It COPY only works for CONTIGUOUS ranges! If you had tried doing this manually on a sheet, you would have received the message

Microsoft Office Excel
That command cannt be used on multiple selections!

Furthermore your Range syntax is incorrect...

Application.Union(Range(cells(i,2), cells(i,3)), cells(i, 5)).Select

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[garrbel]

to SkipVought

You said:
"YOU could answer that question YOUSELF, by trying to COPY multiple, non-contiguous cells"

I tried and it works. I have recorded a macro but can´t do the same in VB.

Range("F23:G23,J23").Select
Selection.Copy

Thanks

 

[SkipVought]

Ah, same row/column does work...

Union(Range("F23:G23"), Range("J23")).Copy

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[garrbel]

To SkipVought again
Of course this code works
Union(Range("F23:G23"), Range("J23")).Copy

But when I tried this I get a error -- Methode Range of object _Global failed


Application.Union (Range(Cells(i + 1, 1))), Range((Cells(i + 1, 3))).Copy

So in that case it don't work?

 

[SkipVought]

Bad syntax!

What is your INTENT with

Union (Range(Cells(i + 1, 1))), Range((Cells(i + 1, 3)))

assuming that i = 1 , Cells(i + 1, 1) references A2 and Cells(i + 1, 3) reference C2.

If you mean Union([A2],[C2]), then...

Application.Union(Cells(i + 1, 1), Cells(i + 1, 3)).Copy

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[garrbel]

Application.Union(Cells(i + 1, 1), Cells(i + 1, 3)).Copy

I just tried this and only copy Cells(i + 1, 1)----A2

Returns no error but don't make copy of cells(i+1,3) just for the first one.

 

[SkipVought]

Sorry, but this performs as advertised!

TWO cells copied from col A & C

pasted in A10 & B10...

i = 1
Application.Union(Cells(i + 1, 1), Cells(i + 1, 3)).Copy [a10]

SOURCE
[tt]
ConsID Addressee Salutation
9928 Mrs J Chapman Mrs Chapman
[/tt]
RESULT
[tt]
9928 Mrs Chapman

[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[garrbel]

You are write. It works. sorry

However after this we can not paste in A10 & C10.

But I learn something more

thanks again

 

[SkipVought]

However after this we can not paste in A10 & C10.

WHY?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[garrbel]

Why?

I tried
Application.Union(Cells(i + 1, 1), Cells(i + 1, 3)).Copy
Application.Union(Cells(i, 1), Cells(i, 3)).Select

With this the cells (i,1) and cells (i,3) are select

then with EDIT PAST
return a error: that command cannot be used on multiple selections

 

[SkipVought]

You are quite correct. You cannot do that. Why would you?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[garrbel]

SkipVought I use a worksheet whith 30 columns to have control over water and electricity bills.

First I insert a row before the last used row and then I have to copy several cells. some from the last row and others from the row before.

Now I have to do this cell by cell because they are no CONTIGUOUS.