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