checking for a blank field

[Oldjay]

I have a database that must have an entry in Col b
I want to filter the records (or what ever) and if it finds a blank field in
col b then a msg box displayed

oldjay

 

[Tom Ogilvy]

Dim rng as Range
On Error Resume Next
set rng = Columns(2).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
msgbox "Column B contains blanks"
End if

 

[Guest]

Tom When I run this code I always get the MsgBox even when there are no blank
cells. If I do an End - Arrow down on col b i go to the last record here is
my code

Private Sub CommandButton1_Click() 'This sets up data entry form


MainMenu.Hide ' Hides "What do you want to do today" form

Sheets("Records").Select 'Goes to membership list

Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 'Sorts
membership alphabetically

ActiveSheet.ShowDataForm

Application.Goto Reference:="Database"
Dim rng As Range
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
MsgBox "Column B contains blanks"
End If

Sheets("Form").Select
Range("A1").Select
Range("C10").Select

MainMenu.Show

End Sub

 

[Norman Jones]

Hi Oldjay,

Tom's code works for me.

Perhaps, column B extends further than you imagine: try Ctrl-End to check
the last row.

 

[Guest]

Crrl-End puts me way over and down from my list. I don't know how to delete
these extra rows and columns. I tried to select and delete, delete rows below
the liste and delete columns to the right of the list.

oldjay

 

[Norman Jones]

Hi Oldjay,

See Debra Dalgleish's page on resetting the used range at:

http://www.contextures.com/xlfaqApp.html#Unused

 

[Guest]

Thanks

Hi Oldjay,

See Debra Dalgleish's page on resetting the used range at:

http://www.contextures.com/xlfaqApp.html#Unused

 

[Guest]

Opps I spoke too fast. After I deleted the entire row and cols I did a
Ctrl-End and it went to the last record. I then ran the macro and got the msg
again

 

[Norman Jones]

Hi Oldjay,

Did you see Debra's note #4:

Save the file. Note: In older versions of Excel, you may have to Save, then
close and re-open the file before the used range is reset.

 

[Guest]

I had to reopen the file to reset the used range. it still always opens the
MsgBox

oldjay

 

[Norman Jones]

Hi Oldjay,

If you wish, send me your file.


norman_jones@NOSPAMbtconnectDOTcom

Delete'NOSPAM' and replace 'DOT' with a period (full stop).

 

[Norman Jones]

The OP sent me his workbook and two relevant problems were apparent:

(1) The used range on the sheet of interest needed to be rest to exclude
extraneous blank rows, and

(2) The B1 cell was empty, although this may not have been immediately
obvious as A1 contained a title header which flowed through B1. To resolve
this problem, I changed the OP's code condition:

to:

If rng.count > 1 Then

 

[Guest]

Thanks again Every thing is working

oldjay (an 80 year old fart )

The OP sent me his workbook and two relevant problems were apparent:

(1) The used range on the sheet of interest needed to be rest to exclude
extraneous blank rows, and

(2) The B1 cell was empty, although this may not have been immediately
obvious as A1 contained a title header which flowed through B1. To resolve
this problem, I changed the OP's code condition:

to:

If rng.count > 1 Then

 

[Norman Jones]

Hi Oldjay,

oldjay (an 80 year old fart )

I sincerly hope that your comment was not prompted by a misinterpetation of
:

Here, and conventionally in newsgroup parlance, OP is an abbreviation for
Original poster.

In any event, the design of your workbook would put many younger people to
shame and certainly impressed me..

 

[Guest]

My comment was only to make an excuse for being slow and not realizing that
checking for blanks in col b meant all rows not just the ones within the list
Sorry for any confusion

oldjay

 

[Guest]

Another problem - when I delete a record the MsgBox opens and says "you have
a blank record"

 

[Norman Jones]

Hi Jim,

In your MainMenu code module's CommandButton1_Click event, change:

On Error Resume Next

Set rng = Columns(2).SpecialCells(xlBlanks)
On Error GoTo 0
' If Not rng Is Nothing Then
If rng.Count > 1 Then

to:

On Error Resume Next
Set rng = Range("Database").Columns(2).SpecialCells(xlBlanks)
On Error GoTo 0

If Not rng Is Nothing Then

 

[Guest]

That didn't do it. I added a new record with out a Last name. Hit closed.
then added a last name to the bad record. Hit Enter and then clicked on Main
Memu. Every thing OK to this point. Whent back to the DataForm and deleted
the newly added record I got the MsgBox again. The Used Range is one row
below the last record.

 

[Norman Jones]

Hi Jim,

I amended your userform code as suggested in my previous post.

I then invoked the dataform (using your userform menu) and added a new
record which consisted (solely) of a forename "JOE". As expected (and
intended), I received a msgbox notification of a missing surname. I then
re-invoked the dataform, located the defective record and appended the
missing surname BLOGGS. To be consistent with your situation, I then
reopened the dataform and deleted the JOE BLOGGS record. The record was
deleted without problem and without any specious alert.

Incidentally, precisely because deleting records via the dataform does not
reset the used range, I changed your (implicit) used range condition to use
the database range instead. Consequently, for these purposes, the extent of
the used range has no relevance.

I have, therefore, sent you the amended file and I invite you to retest.

 

[Norman Jones]

OldJay responded in a private email:

'=================>>
Norman

Every thing seems to be OK. I really appricate your efforts

How do you change (implicit) used range condition to use

the database range?

oldjay
'<<=================

Hi OldJay,

Your original condition looked for blank cells in column B using the
SpecialCells method. The SpecialCells method, unless explicitly further
restricted, operates on the used range.

As this caused problems relating to the deletion of database records, I
changed the control area to the dynamic Database range which expands and
contracts as records are added or deleted via the menu driven dataform.
Hence my suggestion to change:

to