Help getting count of non-blank cells

[Casey]

Hi,
I am attempting to count the non-blank cells in an 8 cell range and failing miserably. I have included in the code below a couple of other avenues I've tried. They are commented out. Setting a watch on colindex and cnt show that colindex is looping as expected, however the cnt seems to only pick up formula cells. I need it to cnt any cell in the range that contains any input.

Here is the Code

Private Sub CommandButton4_Click()
Dim rngEntryBottomRow As Range
Dim Msg As Integer
Dim Response As Integer
Dim colIndex As Integer
Dim cnt As Integer

Application.EnableEvents = False
Application.ScreenUpdating = False

Set rngEntryBottomRow = Range("Below_Entry_Bottom_Row").Offset(-1)
cnt = 0
For colIndex = 1 To 8
With rngEntryBottomRow.Cells(0, colIndex)
If .HasFormula Or .Value > 0 Then cnt = cnt + 1
'Here are two other iterations I have tried.
'If .HasFormula Or .Text <> "" Then cnt = cnt + 1
'If Application.WorksheetFunction.CountA(rngEntryBottomRow.Cells(0, colIndex)) = 1 Then cnt = cnt + 1
End With
Next colIndex

If cnt > 3 Then
Msg = MsgBox("You are attempting to Delete a Row that contains User Input." _
& " Delete Row Failed", vbOKOnly + vbCritical, "Can Not Delete Row with Information")
If Response = 1 Or 2 Then GoTo RET
End If
If cnt = 3 Then
With rngEntryBottomRow
.EntireRow.Delete
End With
End If

RET:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[Don Guillett]

this will also count inadvertent space bar

Sub countnonblank()
MsgBox Columns("f").SpecialCells(xlConstants).Count
End Sub


--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Casey" <Casey.27z1pa_1147902600.6581@excelforum-nospam.com> wrote in
message news:Casey.27z1pa_1147902600.6581@excelforum-nospam.com...
>
> Hi,
> I am attempting to count the non-blank cells in an 8 cell range and
> failing miserably. I have included in the code below a couple of other
> avenues I've tried. They are commented out. Setting a watch on colindex
> and cnt show that colindex is looping as expected, however the cnt seems
> to only pick up formula cells. I need it to cnt any cell in the range
> that contains any input.
>
> Here is the Code
>
> Private Sub CommandButton4_Click()
> Dim rngEntryBottomRow As Range
> Dim Msg As Integer
> Dim Response As Integer
> Dim colIndex As Integer
> Dim cnt As Integer
>
> Application.EnableEvents = False
> Application.ScreenUpdating = False
>
> Set rngEntryBottomRow = Range("Below_Entry_Bottom_Row").Offset(-1)
> cnt = 0
> For colIndex = 1 To 8
> With rngEntryBottomRow.Cells(0, colIndex)
> If .HasFormula Or .Value > 0 Then cnt = cnt + 1
> 'Here are two other iterations I have tried.
> 'If .HasFormula Or .Text <> "" Then cnt = cnt + 1
> 'If
> Application.WorksheetFunction.CountA(rngEntryBottomRow.Cells(0,
> colIndex)) = 1 Then cnt = cnt + 1
> End With
> Next colIndex
>
> If cnt > 3 Then
> Msg = MsgBox("You are attempting to Delete a Row that contains User
> Input." _
> & " Delete Row Failed", vbOKOnly + vbCritical, "Can Not Delete Row
> with Information")
> If Response = 1 Or 2 Then GoTo RET
> End If
> If cnt = 3 Then
> With rngEntryBottomRow
> EntireRow.Delete
> End With
> End If
>
> RET:
> Application.ScreenUpdating = True
> Application.EnableEvents = True
> End Sub
>
>
> --
> Casey
>
>
> ------------------------------------------------------------------------
> Casey's Profile:
> http://www.excelforum.com/member.php...fo&userid=4545
> View this thread: http://www.excelforum.com/showthread...hreadid=543101
>

[Casey]

Don,
Appreciate the feedback, but I can't seem to incorporate your suggestion into my code. I've tried a couple of methods. See Below.

Private Sub CommandButton4_Click()
Dim rngEntryBottomRow As Range
Dim Msg As Integer
Dim Response As Integer
Dim colIndex As Integer
Dim cnt As Integer

Application.EnableEvents = False
Application.ScreenUpdating = False

Set rngEntryBottomRow = Range("Below_Entry_Bottom_Row").Offset(-1)
cnt = 0

For colIndex = 1 To 8
With rngEntryBottomRow.Cells(0, colIndex)
If .SpecialCells(xlConstants) > 0 Then cnt = cnt + 1
'If .SpecialCells(xlConstants)= True Then cnt = cnt + 1
End With
Next colIndex

If cnt > 3 Then
Msg = MsgBox("You are attempting to Delete a Row that contains User Input." _
& " Delete Row Failed", vbOKOnly + vbCritical, "Can Not Delete Row with Information")
If Response = 1 Or 2 Then GoTo RET
End If
If cnt = 3 Then
With rngEntryBottomRow
.EntireRow.Delete
End With
End If

RET:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[Casey]

I've tried all the options below, I can't get a cell with text of a number to be counted. Do I need a new approach or can someone fix my code.

Here is the code to date and the approaches commented out.

Private Sub CommandButton4_Click()
Dim rngEntryBottomRow As Range
Dim Msg As Integer
Dim Response As Integer
Dim colIndex As Integer
Dim cnt As Integer

Application.EnableEvents = False
Application.ScreenUpdating = False

Set rngEntryBottomRow = Range("Below_Entry_Bottom_Row").Offset(-1)
cnt = 0

For colIndex = 1 To 8
With rngEntryBottomRow.Cells(0, colIndex)
If .HasFormula Or .Value <> "" Then cnt = cnt + 1
'If Not .Value Is Null Then cnt = cnt + 1 '<<<<<<<<<<<<<Different approaches
'If .SpecialCells(xlConstants) > 0 Then cnt = cnt + 1
'If .SpecialCells(xlConstants)= True Then cnt = cnt + 1
End With
Next colIndex

If cnt > 3 Then
Msg = MsgBox("You are attempting to Delete a Row that contains User Input." _
& " Delete Row Failed", vbOKOnly + vbCritical, "Can Not Delete Row with Information")
If Response = 1 Or 2 Then GoTo RET
End If
If cnt = 3 Then
With rngEntryBottomRow
.EntireRow.Delete
End With
End If

RET:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub