rows(i) vs rows(i).cells

[Microsoft Forum]

Hi all,

I have the following program:
*****************************
Function IsBlankRange(Rng As Range) As Boolean
Dim r As Range
IsBlankRange = True
For Each r In Rng
If Not IsEmpty(r) Then
IsBlankRange = False
Exit Function
End If
Next
End Function

Sub TableOnly()

Dim tbl As Range
Dim IsTopRowBlank As Boolean
Dim IsBtmRowBlank As Boolean
Dim IsLeftColBlank As Boolean
Dim IsRightColBlank As Boolean
Dim T As Range

Set T = ActiveCell.CurrentRegion

If IsBlankRange(T.Columns(1).Cells) Then IsLeftColBlank = True
If IsBlankRange(T.Rows(1).Cells) Then IsTopRowBlank = True
If IsBlankRange(T.Columns(T.Columns.Count).Cells) Then IsRightColBlank =
True
If IsBlankRange(T.Rows(T.Rows.Count).Cells) Then IsBtmRowBlank = True

End Sub
**************************************

But if I delete the ".Cells" in the last four lines (excluding the "End Sub"
line) of the program, it won't work. Why is that? Seems to me that
"T.Rows(i)" is just a range to me. Please advise.

Frederick Chow
Hong Kong.

 

[Tom Ogilvy]

set T = Range("A1").CurrentRegion
? T.Address
$A$1:$B$86
? t.count
172
? T.Columns(1).Count
1
? t.columns(1).Cells.count
86

so you see that without the "cells", you have a column type range object
which has a count of 1. This causes your problem. Adding cells breaks it
down into its component cells.

built in worksheet functions don't seem to be affected by this difference:

? application.CountA(T.columns(1))
86
? application.countA(T.columns(1).Cells)
86

 

[Microsoft Forum]

Hi Tom,

Thanks for your response. However, why the following statement work wihtout
using the ".Cells" then?

Columns("B").SpecialCells(xlCellTypeConstants, xlNumbers).Select

Frederick Chow
Hong Kong.

 

[Microsoft Forum]

Then why can the following statement be executed properly without the
".Cells"?

Columns("B").Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Select

 

[Tom Ogilvy]

Because SpecialCells can only work on individual cells within the base range
and the authors of the code chose to handle the situation you describe
rather than throwing an error.

Are you grading my paper? <g>

 

[Microsoft Forum]

Haha.. not really, just curious about that.

Well, it seems to me that Microsoft has implemented the rows(i) or
columns(i) in somewhat inconsistent manner..

Sometimes it requires rows(i).cells, sometimes it doen't require
".cells"....

Frederick Chow
Hong Kong.

 

[Tom Ogilvy]

And that is the way of it - however, it usually is not incorrect to include
the cells, so when in doubt use the cells.