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

oldjay

"Norman Jones" wrote:

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.


---
Regards,
Norman


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

"Oldjay" wrote:

Thanks

"Norman Jones" wrote:

Hi Oldjay,

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

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


---
Regards,
Norman


"Oldjay" wrote in message
...
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" wrote:

Hi Oldjay,

Tom's code works for me.

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

---
Regards,
Norman



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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

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