Checking row for data

C

CR

I have a worksheet with 16 columns and 54 rows.
Each row has a range D6 to S6, D7 to S7 ect.

I want to run a macro that checks each range to verify that only 6 columns
in each row have data entered.
I guess a dialog box or something to tell me which ranges have <6 or >6 in
them.

Thanks, much appreciated

CR
 
R

Rick Rothstein

Something like this maybe?

Sub CheckForSixItems()
Dim RW As Long
Dim Items As Long
Dim Answer As String
' Change the 1 to 54 to your actual row range
For RW = 1 To 54
Items = WorksheetFunction.CountA(Range(Cells(RW, "D"), Cells(RW, "S")))
If Items < 6 Then
Answer = Answer & "Row " & RW & " < 6" & vbCrLf
ElseIf Items > 6 Then
Answer = Answer & "Row " & RW & " > 6" & vbCrLf
End If
Next
MsgBox Answer
End Sub
 
C

CR

Is there a way to have the message box give the value of column C in each
range instead of the row number?

Thanks Again

CR
 
R

Rick Rothstein

Here is one possible way...

Sub CheckForSixItems()
Dim RW As Long
Dim Items As Long
Dim Answer As String
' Change the 1 to 54 to your actual row range
For RW = 1 To 54
Items = WorksheetFunction.CountA(Range(Cells(RW, "D"), Cells(RW, "S")))
If Items < 6 Then
Answer = Answer & "Row " & RW & ": " & Cells(RW, "C").Value & _
" (Count < 6)" & vbCrLf
ElseIf Items > 6 Then
Answer = Answer & "Row " & RW & ": " & Cells(RW, "D").Value & _
" (Count > 6)" & vbCrLf
End If
Next
MsgBox Answer
End Sub
 
C

CR

Thank you, I made that work!
You guys are amazing. I only try to do something like this every couple of
years and would have been frustrated for days trying to do what took you
minutes.

CR
 
Top