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

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

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

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

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

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