Using Counta with a range
 

I am trying to look at a range of cells to determine if the row is empty
within this range. But the code below is only looking at the first cell, not
the selected range.


Do

Empty_Check = Application.CountA(Cells(ActiveCell.Row,
1).Range("B1:N1").Select)
MsgBox (Str(Empty_Check))
ActiveCell.Offset(1, 0).Range("A1").Select

Loop Until Empty_Check = 0

Using Counta with a range
 

Here are two different approaches that work for me.

Approach #1 - cycle through the range and stop when finding first empty
cell
Sub StopAtEmpty()
For Each cell In Range("A1:A8")
If cell.Value = "" Then
MsgBox cell.Address & " is empty"
Exit Sub
End If
Next cell
End Sub

Approach #2 - cycle through the range and report status of each cell
Sub CheckEmpty()
For Each cell In Range("A1:A8")
If cell.Value = "" Then
MsgBox cell.Address & " is empty"
Else
MsgBox cell.Address & " is not empty"
End If
Next cell
End Sub


- John
www.johnmichl.com/exceltips.htm

Michael Whitney wrote:
I am trying to look at a range of cells to determine if the row is empty
within this range. But the code below is only looking at the first cell, not
the selected range.


Do

Empty_Check = Application.CountA(Cells(ActiveCell.Row,
1).Range("B1:N1").Select)
MsgBox (Str(Empty_Check))
ActiveCell.Offset(1, 0).Range("A1").Select

Loop Until Empty_Check = 0

Using Counta with a range
 

Hi Michael,

Very simillar but this will count the number of empty cell in any given range:


Sub Count_blank_in_range()
Dim c_cell As Range
Dim c_count As Integer
Set c_cell = Range("A1:A10")
For Each cell In c_cell
If cell = "" Then
c_count = c_count + 1
End If
Next cell
MsgBox (c_count)
End Sub

HTH

Simon

Michael Whitney wrote:
I am trying to look at a range of cells to determine if the row is empty
within this range. But the code below is only looking at the first cell, not
the selected range.

Do

Empty_Check = Application.CountA(Cells(ActiveCell.Row,
1).Range("B1:N1").Select)
MsgBox (Str(Empty_Check))
ActiveCell.Offset(1, 0).Range("A1").Select

Loop Until Empty_Check = 0

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via http://www.officekb.com

Using Counta with a range
 

Thanks - It looks like I will will have to use this method to check each cell
in the row and only stop my loop if the c_count variable stays at 0. I guess
the counta application command just does not work, correct?

"smw226 via OfficeKB.com" wrote:

Hi Michael,

Very simillar but this will count the number of empty cell in any given range:


Sub Count_blank_in_range()
Dim c_cell As Range
Dim c_count As Integer
Set c_cell = Range("A1:A10")
For Each cell In c_cell
If cell = "" Then
c_count = c_count + 1
End If
Next cell
MsgBox (c_count)
End Sub

HTH

Simon

Michael Whitney wrote:
I am trying to look at a range of cells to determine if the row is empty
within this range. But the code below is only looking at the first cell, not
the selected range.

Do

Empty_Check = Application.CountA(Cells(ActiveCell.Row,
1).Range("B1:N1").Select)
MsgBox (Str(Empty_Check))
ActiveCell.Offset(1, 0).Range("A1").Select

Loop Until Empty_Check = 0

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via http://www.officekb.com

Using Counta with a range
 

One way, using your existing code:

Do
Range("B" & ActiveCell.Row & _
":N" & ActiveCell.Row).Select
Empty_Check = _
Application.CountA(Range("B" & ActiveCell.Row & _
":N" & ActiveCell.Row))
MsgBox (Str(Empty_Check))
ActiveCell.Offset(1, 0).Range("A1").Select
Loop Until Empty_Check = 0

Regards

Trevor


"Michael Whitney" <Michael wrote in
message ...
I am trying to look at a range of cells to determine if the row is empty
within this range. But the code below is only looking at the first cell,
not
the selected range.


Do

Empty_Check = Application.CountA(Cells(ActiveCell.Row,
1).Range("B1:N1").Select)
MsgBox (Str(Empty_Check))
ActiveCell.Offset(1, 0).Range("A1").Select

Loop Until Empty_Check = 0