Referencing a Range with Variables

Question

I absolutely detest the range object. How does it read cell references? In the code below, I am trying to search the last occupied column on a sheet. So, I start off from column 1000 and down to column 1:

EndCol = 1001: Do
    EndCol = EndCol - 1
    Set rScanArea = .range(.Cells(1, EndCol), .Cells(1048576, EndCol))
Loop While WorksheetFunction.CountA(rScanArea) = 0 and EndCol > 0

This did not work, but it did after I appended ".address()" after my cell references. So I guess in my original code, vba was reading the values of the cells. However, in the past, there were instances where the address attribute is not needed. For example, the following worked:

.Cells(i, 4).Hyperlinks.Add Range(.Cells(i, 4), .Cells(i, 4)), strLink

score 2 · Accepted Answer · edited May 23 '17 at 12:22

I, for one, have no error setting a range with the method

set rngRange = Range(cells(1,1), cells(1,5))

There are several ways to set ranges. If you want to use the syntax set rngRange = Range("A1:A2") then you have to use addresses which are strings.

You can also set a range of ranges. This uses addresses;

set biggerRange = (rng1.address & ":" & rng2.address)

You can also refer to ranges with square brackets but it is not reccomended.

Also, aside fromk the question, your way of finding the last column is not that reliable. I suggest reading this Excel VBA- Finding the last column with data for better ways.

Ok, TY. I did not realize that there exists a built-in function for the job. — Andy Tam, Aug 25 '15 at 14:39

score 1 · Answer 2 · edited May 23 '17 at 12:06

1

If you only want to get the last column with data, check this:

Excel VBA- Finding the last column with data

Cheers

edited May 23 '17 at 12:06

Community

1
1

answered Aug 25 '15 at 15:45

WltrRpo

263
2
13

Referencing a Range with Variables

2 Answers2