VBA Excel: selecting a variable range Solved

Question

Hello everyone,

I need to select a range of cells based on a variable i, which represents the desired row number. After researching on ccm.net and other forums, I found the following code:

Dim MaPlage As Range

MaPlage = Range("A" & i & ":L" & i)

I'm using Excel 2003 and something is off: VBA returns "runtime error 91: object variable or With block variable not set". Really, I don't understand. Does anyone have an idea?

Thanks in advance,
Pyanitsa.
-------------------------------------------------------------------
V-31-10-2014
Hello, you can also write MaPlage like this:

Dim MaPlage As Range
Dim i As Integer

Set MaPlage = Range("A" & i, "L" & i)
'The row i from column A to column L will correspond to MaPlage

To obtain a p

thev · Accepted Answer

Range("A" & i & ":L" & i) represents a range of cells, that is to say an "Excel object" just like a workbook, a sheet, or a cell.
To assign a variable (= MyRange) to an object, you need to use the SET instruction.

That said, to assign your variable range of cells, I prefer the instruction:

set MyRange = Columns("A:L").Rows(i)

which I find more readable.

thev · Answer

set MaPlage = Range("A" & i & ":L" & i)

Pyanitsa · Answer

However, I have already assigned values to variables (integers) without using 'set'.

Your syntax is quite good! I wasn't familiar with it. I believe we can also use 'Cell' instead of 'Range', but the latter seems more manageable.

Thanks again!

c2muz · Answer

Hello,
Beginner in VBA Excel 2003
how to select a range of data, for example: from A1 to Zlast non-empty row?
After importing an external file, the number of imported rows varies and therefore the number of the last row is never the same.
Thank you in advance

Bosskev · Answer

J'ai lu toutes vos réponses, mais est-ce que le VBA sur 2010 est le même que le VBA sur Excel 2003 ?

Paul*Paul · Answer

Hello everyone,

I need to select a range of cells based on a variable i, which represents the desired row number. After researching on ccm.net and other forums, I found the following code:

Dim MaPlage As Range

MaPlage = Range("A" & i & ":L" & i)

I'm using Excel 2003 and something is off: VBA returns "runtime error 91: object variable or With block variable not set". Really, I don't understand. Does anyone have an idea?

Thanks in advance,
Pyanitsa.
-------------------------------------------------------------------
V-31-10-2014
Hello, you can also write MaPlage like this:

Dim MaPlage As Range
Dim i As Integer

Set MaPlage = Range("A" & i, "L" & i)
'The row i from column A to column L will correspond to MaPlage

To obtain a p

pijaku · Answer

Hello,  Just to test, try this: Dim MyRange As Range Dim i As Integer i = 1 Set MyRange = Range("A" & i & ":L"& i) MyRange.Select  -- &#127932; Best regards, Franck &#127926;

VBA Excel: selecting a variable range

7 réponses

Access issue to my "contact me" profile

Windows spotlight is not working.

Samsung j5 unable to answer

My nikon coolpix s3000 won't turn on anymore.

Bug with the "backspace" key

Do you know maillotsofficiels.fr?

Wii balance board issues

Focusing issue on canon eos 450d

Why do i need to press f1 to start

Wi-fi connection via livebox