I have selected a range with 20 rows and 10 columns.
I want to ensure that every rows in the selectin has
one value in it. Also, it should be ensured that every row has
just one value. ie. no two columns in the same row have data in
it. I tried the following code. I need the suggestions in it.
Thanks in advance.

Sub isrowempty(emptyrow As Boolean)
'to check if the selected row is empty or not
emptyrow = False
For i = 1 To Selection.Rows.Count
If IsEmpty(Selection.Range("A" & i)) Then
If IsEmpty(Selection.Range("B" & i)) Then
If IsEmpty(Selection.Range("C" & i)) Then
Mymsgbox "Selected Range Contains empty Row." _
& vbNewLine & " No Range in the selection
should be empty."
emptyrow = True
Exit Sub
End If
End If
End If

Next
End Sub

I'm not sure why you have set your code up as a Sub where you change its
single argument as opposed to setting it up as a Function that returns a
value (a Boolean value in your case), but I went with your set up in my code
below (including using your MyMsgBox subroutine rather than VB's built in
MsgBox). I also changed the message you were passing to your MyMsgBox
subroutine as it did not match the description you gave in your questions.
Anyway, give this a try and see if you can make use of it...

Sub IsRowEmpty(EmptyRow As Boolean)
Dim R As Range
For Each R In Selection.Rows
If R.Cells.Count - R.SpecialCells(xlCellTypeBlanks).Count <> 1 Then
MyMsgBox "Not all rows in selected range have only one value."
EmptyRow = True
Exit Sub
End If
Next
End Sub

--
Rick (MVP - Excel)



"Subodh" <(E-Mail Removed)> wrote in message
news:f168cfd7-6a0f-48ba-8ec5-(E-Mail Removed)...
> I have selected a range with 20 rows and 10 columns.
> I want to ensure that every rows in the selectin has
> one value in it. Also, it should be ensured that every row has
> just one value. ie. no two columns in the same row have data in
> it. I tried the following code. I need the suggestions in it.
> Thanks in advance.
>
> Sub isrowempty(emptyrow As Boolean)
> 'to check if the selected row is empty or not
> emptyrow = False
> For i = 1 To Selection.Rows.Count
> If IsEmpty(Selection.Range("A" & i)) Then
> If IsEmpty(Selection.Range("B" & i)) Then
> If IsEmpty(Selection.Range("C" & i)) Then
> Mymsgbox "Selected Range Contains empty Row." _
> & vbNewLine & " No Range in the selection
> should be empty."
> emptyrow = True
> Exit Sub
> End If
> End If
> End If
>
> Next
> End Sub

Provided the selected range is no enormous:
Sub tryme()
Set mydata = Selection
myrows = mydata.Rows.Count
mycols = mydata.Columns.Count
For j = 1 To myrows
mycount = 0
For k = 1 To mycols
If mydata(j, k) <> "" Then mycount = mycount + 1
Next k
If mycount = 0 Then MsgBox "Empty row " & j
If mycount > 1 Then MsgBox "Too much data in row " & j
Next j
End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Subodh" <(E-Mail Removed)> wrote in message
news:f168cfd7-6a0f-48ba-8ec5-(E-Mail Removed)...
> I have selected a range with 20 rows and 10 columns.
> I want to ensure that every rows in the selectin has
> one value in it. Also, it should be ensured that every row has
> just one value. ie. no two columns in the same row have data in
> it. I tried the following code. I need the suggestions in it.
> Thanks in advance.
>
> Sub isrowempty(emptyrow As Boolean)
> 'to check if the selected row is empty or not
> emptyrow = False
> For i = 1 To Selection.Rows.Count
> If IsEmpty(Selection.Range("A" & i)) Then
> If IsEmpty(Selection.Range("B" & i)) Then
> If IsEmpty(Selection.Range("C" & i)) Then
> Mymsgbox "Selected Range Contains empty Row." _
> & vbNewLine & " No Range in the selection
> should be empty."
> emptyrow = True
> Exit Sub
> End If
> End If
> End If
>
> Next
> End Sub

One more...

Option Explicit
Sub testme()
Dim ok As Boolean

Call IsRowEmpty(ok)

If ok = True Then
MsgBox "Selected Range Contains empty Row." _
& vbNewLine & " No Range in the selection should be empty."
Else
MsgBox "Ok to continue"
End If

End Sub
Sub IsRowEmpty(EmptyRow As Boolean)

Dim myArea As Range
Dim myRow As Range
'to check if the selected row is empty or not
EmptyRow = False

For Each myArea In Selection.Areas
For Each myRow In myArea.Rows
If Application.CountA(myRow) = 0 Then
EmptyRow = True
Exit For
End If
Next myRow
Next myArea

End Sub

=counta() will count cells that are non-empty.

This includes cells that contain formulas that evaluate to "" (or were once ""
and were converted to values, but not cleared!).

I would add some other checks to make sure the range is exactly what you want
(20 rows by 10 columns).


Subodh wrote:
>
> I have selected a range with 20 rows and 10 columns.
> I want to ensure that every rows in the selectin has
> one value in it. Also, it should be ensured that every row has
> just one value. ie. no two columns in the same row have data in
> it. I tried the following code. I need the suggestions in it.
> Thanks in advance.
>
> Sub isrowempty(emptyrow As Boolean)
> 'to check if the selected row is empty or not
> emptyrow = False
> For i = 1 To Selection.Rows.Count
> If IsEmpty(Selection.Range("A" & i)) Then
> If IsEmpty(Selection.Range("B" & i)) Then
> If IsEmpty(Selection.Range("C" & i)) Then
> Mymsgbox "Selected Range Contains empty Row." _
> & vbNewLine & " No Range in the selection
> should be empty."
> emptyrow = True
> Exit Sub
> End If
> End If
> End If
>
> Next
> End Sub

--

Dave Peterson