Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
To determine if selection range contains empty rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
To determine if selection range contains empty rows
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
To determine if selection range contains empty rows
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
To determine if selection range contains empty rows
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove empty rows from a range | Excel Programming | |||
Loop to determine number of rows range | Excel Programming | |||
deleting empty rows within a range | Excel Programming | |||
empty rows in range | Excel Programming | |||
Selection range to last non empty cell | Excel Programming |