rng.Count ====results wrong value?
 

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

rng.Count ====results wrong value?
 

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

--
__________________________________
HTH

Bob

"shiro" wrote in message
...
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

rng.Count ====results wrong value?
 

On Jun 19, 4:13*pm, "shiro" wrote:
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

rng.Count ====results wrong value?
 

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


"Nayab" wrote in message
...
On Jun 19, 4:13 pm, "shiro" wrote:
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

rng.Count ====results wrong value?
 

MsgBox Application.Count(myRng)


--
__________________________________
HTH

Bob

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


"Nayab" wrote in message
...
On Jun 19, 4:13 pm, "shiro" wrote:
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

rng.Count ====results wrong value?
 

On Jun 19, 4:33*pm, "shiro" wrote:
And then,
how to manipulate the msgbox so that it says
0 if actually no data in cell Q10 and Q11?

"Nayab" wrote in message

...
On Jun 19, 4:13 pm, "shiro" wrote:





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- 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

rng.Count ====results wrong value?
 

Or maybe...

MsgBox Application.CountA(myRng)

(just in case <vbg)

Bob Phillips wrote:

MsgBox Application.Count(myRng)

--
__________________________________
HTH

Bob

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


"Nayab" wrote in message
...
On Jun 19, 4:13 pm, "shiro" wrote:
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



--

Dave Peterson

rng.Count ====results wrong value?
 

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

Rgds,

Shiro


"Nayab" wrote in message
...
On Jun 19, 4:33 pm, "shiro" wrote:
And then,
how to manipulate the msgbox so that it says
0 if actually no data in cell Q10 and Q11?

"Nayab" wrote in message

...
On Jun 19, 4:13 pm, "shiro" wrote:





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- 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