VBA Not selecting range properly

[DaLiMan]

Hello,

I want to create a For/Next loop to select a range.
When I try out my code it does not select the way I want.

I expect to select only D6 and M6, but it selects D6 to M6 instead.

Worksheets("sheet1").Range("D" & 6, "M" & 6).Select

When I try this line it works fine, but cannot be used in a For/Next loop.

Worksheets("sheet1").Range("D6,M6").Select

Is anyone able to tell me what i'm doing wrong here?

Thanx,
Daniel

 

[Weaver]

Try

Worksheets("sheet1").Range("D" & 6, ",M" & 6).Select

On a further point, usually, you do not need to 'select' cells before doing anytihng to them. For instance

range("A1").select
selection.value = "Test this"

is the same as

range("A1").value = "Test this"

Over larger ranges and in bigger loops, the second method is far more efficient.

 

[DaLiMan]

Hi Weaver,

Thanx for the quick reply!
Your suggestion however gives me an ERROR 1004.

Any other suggestions?

 

[p45cal]

Range("D" & 6 & ",M" & 6).Select

 

[DaLiMan]

Range("D" & 6 & ",M" & 6).Select

That's the trick....

However selecting the sheet in the same code does not work.
So we end up with 2 lines.

Worksheets("sheet1").Select
Range("D" & 6 & ",M" & 6).Select

Thanx for the help guy's.

 

[p45cal]

You can't select a range on a sheet without it being the active sheet.
You probably don't need to select these cells; what are you doing with them?

 

[DaLiMan]

You can't select a range on a sheet without it being the active sheet.

I found selecting a range in sheet1 from sheet2 being active works fine if sheet1 is being prefixed in the code. However the range must be in 1 piece and not random.

You probably don't need to select these cells; what are you doing with them?

Maybe not, but I do not know another way.
I need these 2 colums to be copied to another (new) excel workbook.
I can't just copy top to bottom, because I'm limited to 100 items. (this is because of an external program which uploads the new workbook.)
So instead I'm counting from 1 to 100, then 101 tot 200 etc....pasting this in the upload workbook 1 or 2 or 3.

Big workaround I guess...
However, when it works it saves a lot of manual labour.....



Daniel

 

[p45cal]

as a demo, insert a new blank sheet in the same workbook as the D6/M6 cells on Sheet1 you want to copy, and keeping that new sheet the active sheet, execute:

union(sheets("sheet1").range("D" & 6).Resize(100),sheets("sheet1").range("M"&  6).Resize(100)).copy Range("C1")

any good?

 

[DaLiMan]

as a demo, insert a new blank sheet in the same workbook as the D6/M6 cells on Sheet1 you want to copy, and keeping that new sheet the active sheet, execute:

union(sheets("sheet1").range("D" & 6).Resize(100),sheets("sheet1").range("M"&  6).Resize(100)).copy Range("C1")

any good?

Yes, very good.
But how do I implement this with a new workbook, is that possible?

 

[p45cal]

Yes, very good.
But how do I implement this with a new workbook, is that possible?

Yes it is. What's your current code look like?