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

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

--
Rick (MVP - Excel)


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

Outstanding, works like a charm

Thank you very much

CR



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

--
Rick (MVP - Excel)


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

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


"CR" wrote in message
m...
Outstanding, works like a charm

Thank you very much

CR



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

--
Rick (MVP - Excel)


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

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

--
Rick (MVP - Excel)


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


"CR" wrote in message
m...
Outstanding, works like a charm

Thank you very much

CR



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

--
Rick (MVP - Excel)


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

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

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

--
Rick (MVP - Excel)


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


"CR" wrote in message
m...
Outstanding, works like a charm

Thank you very much

CR



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

--
Rick (MVP - Excel)


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