checking for a blank field

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" <jauld1@hotmail.com> wrote in message
news:%236p0d7G$FHA.2156@TK2MSFTNGP11.phx.gbl...
> 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" <jauld1@hotmail.com> wrote in message
> news:%236p0d7G$FHA.2156@TK2MSFTNGP11.phx.gbl...
> > 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" <Oldjay@discussions.microsoft.com> wrote in message
news:D1244FF0-FEDA-4D24-BD43-2F77B6EF8A2C@microsoft.com...
> 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" <jauld1@hotmail.com> wrote in message
>> news:%236p0d7G$FHA.2156@TK2MSFTNGP11.phx.gbl...
>> > 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" <Oldjay@discussions.microsoft.com> wrote in message
> news:D1244FF0-FEDA-4D24-BD43-2F77B6EF8A2C@microsoft.com...
> > 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" <jauld1@hotmail.com> wrote in message
> >> news:%236p0d7G$FHA.2156@TK2MSFTNGP11.phx.gbl...
> >> > 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" <Oldjay@discussions.microsoft.com> wrote in message
news:1EC075AE-B63D-45B6-951E-AF081C9D7C40@microsoft.com...
> 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" <Oldjay@discussions.microsoft.com> wrote in message
>> news:D1244FF0-FEDA-4D24-BD43-2F77B6EF8A2C@microsoft.com...
>> > 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" <jauld1@hotmail.com> wrote in message
>> >> news:%236p0d7G$FHA.2156@TK2MSFTNGP11.phx.gbl...
>> >> > 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
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>