generating a range from other cells excel vba

Question

I am not sure if this can be done to start with.

i am iterating through some cells and at some point I want to define a range like this:

Set rngtmp = Range(f.Column & c.Row & ":" & g.Column & c.Row)

f and g are pointing to single cells and they are okay (I mean that they work just fine) because I am also doing some operations taking them as a reference and they work.

c is the cell that i am currently at ( since I am iterating through all the cells). The range is always empty and I don't understand why.

f.column is smaller than g.column

Possible duplicate of [Create excel ranges using column numbers in vba?](http://stackoverflow.com/questions/13157363/create-excel-ranges-using-column-numbers-in-vba) — eirikdaude, Feb 26 '16 at 13:25

score 3 · Answer 1 · edited May 23 '17 at 12:31

3

You can use the .Cells() property.

Dim wb As Workbook, ws As Worksheet
Dim rngTmp As Range

Set wb = ThisWorkbook
Set ws = wb.Sheets(1)

With ws
    Set rngTmp = .Range(.Cells(c.Row, f.Column), .Cells(c.Row, g.Column))
End With

You cannot use integers to determine the size of a range object; you are required to use the .Cells property to be able to use integers to build a range.

Edit:

As @Scott Holtzman mentioned, you must firstly pass .Row and then .Column into .Cells (the opposite to how you have it shown in your question).

As @eirikdaude mentioned, you can use .Range and then use the .Resize property; this allows you to use integers with the range, instead of having to use the .Cells property. This is an approach which I typically use when writing arrays to a worksheet.

edited May 23 '17 at 12:31

Community

1
1

answered Feb 26 '16 at 13:21

luke_t

2,935
4
22
38

also worth noting how the row property comes first, then the column, as OP had it other way around) – Scott Holtzman Feb 26 '16 at 13:23
You can use integers to determine the size of a range-object, but it's simpler just doing it like you have ;-) – eirikdaude Feb 26 '16 at 13:24
Thanks both - I have updated my answer to include your suggestions. @eirikdaude I assume you were referring to the .Resize property. Otherwise I would be intrigued to hear of how you can also use integers with the range object! – luke_t Feb 26 '16 at 13:34
Well, `activesheet.range(chr(i+65) & 2).select` works for instance, but as I said it is generally easier to just go with `cells` in such cases. The above method will probably run into trouble for i > 26, for instances (does lowercase come after or before uppercase). It was more a case of pointing out that something *can* be done, although it is almost never a good idea to do it ;) – eirikdaude Feb 26 '16 at 13:43
Thanks for confirming. I agree - it's not practical to use this method, but it's certainly possible. – luke_t Feb 26 '16 at 14:39

score 1 · Accepted Answer · answered Feb 26 '16 at 13:22

Use Cells to refer to a single cell. You can then join these in a range:

Sub Test()
    Dim rngtmp As Range
    Dim f As Range
    Dim c As Range
    Dim g As Range

    With ThisWorkbook.Worksheets("Sheet1")
        Set f = .Range("A1")
        Set c = .Range("D8")
        Set g = .Range("L22")
        Set rngtmp = .Range(.Cells(c.Row, f.Column), .Cells(g.Column, c.Row))
    End With
End Sub

Note that .Range and .Cells will refer to Sheet1 due to the With...End With block.
https://msdn.microsoft.com/en-us/library/wc500chb.aspx

score 0 · Answer 3 · answered Feb 26 '16 at 13:24

You can't use .Column as it returns the integer index of the column, not a letter.

Try something along the lines of

set rngtmp = Range(Cells(f.Row, c.Column), Cells(g.Row, c.Column))

of course you will need to reference sheets and cells and such as required

generating a range from other cells excel vba

3 Answers3