I use this technique when I get confused about the type of object I have created by stringing properties.
Within Excel's Visual Basic Editor, create a new module if you do not have an existing one that you wish to use in this way. If you select the module in Project Explorer and click F4, you can change the module's name to "Experiments" say.
Type or copy:
Option Explicit
Sub TestA()
End Sub
I always start my modules with Option Explicit. Look Option Explicit up in VBA Help and it will tell you why this is a good idea.
I have also created an empty sub-routine into which I will type some statements.
Start typing a new statement so you have:
Sub TestA()
Debug.Print Range("B:B").
End Sub
When you type the period at the end of this new line, a pop-up window will show you the available methods and properties. This list will show, as expected, all the methods and properties of a Range. Type "Address" or select Address from the list to get:
Sub TestA()
Debug.Print Range("B:B").Address
End Sub
Click F5 to run this macro and the following will appear in the Immediate Window:
$B:$B
This is the address of all rows in column B which is what you would expect.
Now add two further statements to the macro:
Debug.Print Range("F:F").Address
Debug.Print Union(Range("B:B"), Range("F:F")).Address
Run this macro again and you will get:
$B:$B
$F:$F
$B:$B,$F:$F
Again this is what was expected.
Now add:
Debug.Print Union(Range("B:B"), Range("F:F")).Rows.
The pop-up window that appears will be unchanged because Range.Rows is still a range.
Complete the statement by adding or selecting "Address" and run the macro again to get:
$B:$B
$F:$F
$B:$B,$F:$F
$B:$B,$F:$F
This may not be what you expected but think about it. $B:$B,$F:$F is all rows in columns B and F so adding the property Rows does not change the address.
Now add the following statements to the macro:
Debug.Print Union(Range("B:B"), Range("F:F")).Count
Debug.Print Union(Range("B:B"), Range("F:F")).Rows.Count
Run the macro and these statements will each output an integer. I am using Excel 2003 so I get:
131072
65536
If you are using a later version of Excel, you will get larger integers. The second integer is the number of rows in a worksheet for your version of Excel. The first integer is the number of cells in two columns of a worksheet for your version of Excel.
Now add:
Debug.Print Union(Range("B:B"), Range("F:F")).Rows.Count.
When you type the final period, no pop-up window will appear because an integer has no method or property that you can select in this way. Method .End(xlUp) operates on a range; it is not a property of Count which is why you get "Invalid qualifier".
It is very easy to get oneself confused when stringing properties together. Personally I avoid stringing properties because even if it is faster to run, it takes longer for me to understand and debug. There are situations in which minimising runtime is the top priority but is this one of those cases? How many hours have you wasted with this approach?
Consider:
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim RowMax As Long
Set Rng1 = Range("B:B")
Set Rng2 = Range("F:F")
Set Rng3 = Union(Rng1, Rng2)
RowMax = Rng3.Count
Debug.Print RowMax
Debug.Print Rng3.Find("*", Range("B1"), xlValues, xlWhole, xlByRows, xlPrevious).Row
You do not need RowMax but I have included it so you are absolutely clear what Rng3.Count returns. I have also gone OTT with the ranges. I would be happy to type: Set Rng3 = Union(Range("B:B"), Range("F:F")) because I find it easy to understand.
Method .End(xlUp) operates on a cell. MultiCellRange.End(xlUp).Row is valid syntax but I cannot get it to return useful information. If you want to use .End(xlUp) consider:
Dim RowMaxColB As Long
Dim RowMaxColF As Long
RowMaxColB = Cells(Rows.Count, "B").End(xlUp).Row
RowMaxColF = Cells(Rows.Count, "F").End(xlUp).Row
I agree with Siddharth, Find appears to be the best approach in this situation. However, you should look at this answer of mine, https://stackoverflow.com/a/20849875/973283, to a different question. It includes a macro that demonstrates a selection of methods of finding last rows and columns and shows the situations in which they fail.
