rng.Count ====>results wrong value?

[shiro]

Sub tEST()
Dim LastRow As Long
Dim myRng As Range
Dim WS As Worksheet

Set WS = Sheets("DATA 2") '<<< Ganti jika perlu
With WS
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
Set myRng = .Range("Q11:Q" & LastRow)

MsgBox myRng.Count
End With
End Sub
================================================
Hi all,
is there something wrong with the above code.?
Actually,there is no any data on the .Range("Q11:Q" & LastRow)
Why does the msgbox return the value 2
instead 0?.

rgds,

Shiro

 

[Bob Phillips]

Because it counts the number of cells, not the number of cells with data?

 

[Nayab]

Sub tEST()
    Dim LastRow As Long
    Dim myRng As Range
    Dim WS As Worksheet

    Set WS = Sheets("DATA 2")  '<<< Ganti jika perlu
    With WS
        LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
        Set myRng = .Range("Q11:Q" & LastRow)

    MsgBox myRng.Count
    End With
End Sub
================================================
Hi all,
is there something wrong with the above code.?
Actually,there is no any data on the .Range("Q11:Q" & LastRow)
Why does the msgbox return the value 2
instead 0?.

rgds,

Shiro

Actually it is returning the value of the number of the selected cells
in myRng. So it is actually selecting Q10, Q11 and hence returns 2,
the count of selected cells

 

[shiro]

And then,
how to manipulate the msgbox so that it says
0 if actually no data in cell Q10 and Q11?

Sub tEST()
Dim LastRow As Long
Dim myRng As Range
Dim WS As Worksheet

Set WS = Sheets("DATA 2") '<<< Ganti jika perlu
With WS
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
Set myRng = .Range("Q11:Q" & LastRow)

MsgBox myRng.Count
End With
End Sub
================================================
Hi all,
is there something wrong with the above code.?
Actually,there is no any data on the .Range("Q11:Q" & LastRow)
Why does the msgbox return the value 2
instead 0?.

rgds,

Shiro

Actually it is returning the value of the number of the selected cells
in myRng. So it is actually selecting Q10, Q11 and hence returns 2,
the count of selected cells

 

[Bob Phillips]

MsgBox Application.Count(myRng)

 

[Nayab]

And then,
how to manipulate the msgbox so that it says
0 if actually no data in cell Q10 and Q11?






Actually it is returning the value of the number of the selected cells
in myRng.  So it is actually selecting Q10, Q11 and hence returns 2,
the count of selected cells- Hide quoted text -

- Show quoted text -

Sub tEST()
Dim LastRow As Long
Dim myRng, r As Range
Dim WS As Worksheet

Set WS = Sheets(2) '<<< Ganti jika perlu
i = 0
With WS
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
Set myRng = .Range("Q11:Q" & LastRow)
For Each r In myRng
If (r.Value = "") Then
Else
i = i + 1
End If
Next r
MsgBox myRng.Count
End With
If i = 0 Then
MsgBox "0"
End If
End Sub

 

[Dave Peterson]

Or maybe...

MsgBox Application.CountA(myRng)

(just in case <vbg>)

 

[shiro]

Thank's All,
it's a new lesson.

Rgds,

Shiro

And then,
how to manipulate the msgbox so that it says
0 if actually no data in cell Q10 and Q11?






Actually it is returning the value of the number of the selected cells
in myRng. So it is actually selecting Q10, Q11 and hence returns 2,
the count of selected cells- Hide quoted text -

- Show quoted text -

Sub tEST()
Dim LastRow As Long
Dim myRng, r As Range
Dim WS As Worksheet

Set WS = Sheets(2) '<<< Ganti jika perlu
i = 0
With WS
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
Set myRng = .Range("Q11:Q" & LastRow)
For Each r In myRng
If (r.Value = "") Then
Else
i = i + 1
End If
Next r
MsgBox myRng.Count
End With
If i = 0 Then
MsgBox "0"
End If
End Sub