Data validation and empty cells

range("d1").Validation.add formula1:=
"=$A$1:$A$7",Type:=xlValidateList,operator:=xlBetween


How to avoid empty entries in drop down box if some of cell from A1:A7
are empty?

Thanks

create another range where there are no spaces and use that.

--
Regards,
Tom Ogilvy


"Kris" <[email protected]> wrote in message
news:[email protected]...
>
> range("d1").Validation.add formula1:=
> "=$A$1:$A$7",Type:=xlValidateList,operator:=xlBetween
>
>
> How to avoid empty entries in drop down box if some of cell from A1:A7
> are empty?
>
> Thanks

This should be a good start. It returns the address of the non blank cells...

Private Function NonBlank(ByVal rng As Range) As Range
Dim rngCurrent As Range
Dim rngNonBlank As Range

For Each rngCurrent In rng
If rngCurrent.Value <> "" Then
If rngNonBlank Is Nothing Then
Set rngNonBlank = rngCurrent
Else
Set rngNonBlank = Union(rngCurrent, rngNonBlank)
End If
End If
Next rngCurrent
Set NonBlank = rngNonBlank
End Function

Sub test()
Dim rng As Range

Set rng = NonBlank(Range("A1:A7"))
If rng Is Nothing Then
MsgBox "They are all blank"
Else
MsgBox rng.Address
End If
End Sub

--
HTH...

Jim Thomlinson


"Kris" wrote:

>
> range("d1").Validation.add formula1:=
> "=$A$1:$A$7",Type:=xlValidateList,operator:=xlBetween
>
>
> How to avoid empty entries in drop down box if some of cell from A1:A7
> are empty?
>
> Thanks
>

I get the message something like:
"You can not use unions, intersections or array constants for data
validation criteria"

or are you suggesting building a comma separated string from the result and
using a constant argument?

--
Regards,
Tom Ogilvy



"Jim Thomlinson" <[email protected]> wrote in
message news:[email protected]...
> This should be a good start. It returns the address of the non blank
cells...
>
> Private Function NonBlank(ByVal rng As Range) As Range
> Dim rngCurrent As Range
> Dim rngNonBlank As Range
>
> For Each rngCurrent In rng
> If rngCurrent.Value <> "" Then
> If rngNonBlank Is Nothing Then
> Set rngNonBlank = rngCurrent
> Else
> Set rngNonBlank = Union(rngCurrent, rngNonBlank)
> End If
> End If
> Next rngCurrent
> Set NonBlank = rngNonBlank
> End Function
>
> Sub test()
> Dim rng As Range
>
> Set rng = NonBlank(Range("A1:A7"))
> If rng Is Nothing Then
> MsgBox "They are all blank"
> Else
> MsgBox rng.Address
> End If
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Kris" wrote:
>
> >
> > range("d1").Validation.add formula1:=
> > "=$A$1:$A$7",Type:=xlValidateList,operator:=xlBetween
> >
> >
> > How to avoid empty entries in drop down box if some of cell from A1:A7
> > are empty?
> >
> > Thanks
> >

Oops... For the life of me I thought you could use non-contiguious ranges in
validation arguments... Should have tested it all the way. Give this a try...

Private Function NonBlank(ByVal rng As Range) As String
Dim rngCurrent As Range
Dim strNonBlank As String

For Each rngCurrent In rng
If rngCurrent.Value <> "" Then
If Len(strNonBlank) = 0 Then
strNonBlank = rngCurrent.Value
Else
strNonBlank = strNonBlank & ", " & rngCurrent.Value
End If
End If
Next rngCurrent
NonBlank = strNonBlank
End Function

Sub test()
Dim str As String

str = NonBlank(Range("A1:A7"))
If Len(str) = 0 Then
MsgBox "They are all blank"
Else
MsgBox str
End If
End Sub
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

> I get the message something like:
> "You can not use unions, intersections or array constants for data
> validation criteria"
>
> or are you suggesting building a comma separated string from the result and
> using a constant argument?
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Jim Thomlinson" <[email protected]> wrote in
> message news:[email protected]...
> > This should be a good start. It returns the address of the non blank
> cells...
> >
> > Private Function NonBlank(ByVal rng As Range) As Range
> > Dim rngCurrent As Range
> > Dim rngNonBlank As Range
> >
> > For Each rngCurrent In rng
> > If rngCurrent.Value <> "" Then
> > If rngNonBlank Is Nothing Then
> > Set rngNonBlank = rngCurrent
> > Else
> > Set rngNonBlank = Union(rngCurrent, rngNonBlank)
> > End If
> > End If
> > Next rngCurrent
> > Set NonBlank = rngNonBlank
> > End Function
> >
> > Sub test()
> > Dim rng As Range
> >
> > Set rng = NonBlank(Range("A1:A7"))
> > If rng Is Nothing Then
> > MsgBox "They are all blank"
> > Else
> > MsgBox rng.Address
> > End If
> > End Sub
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Kris" wrote:
> >
> > >
> > > range("d1").Validation.add formula1:=
> > > "=$A$1:$A$7",Type:=xlValidateList,operator:=xlBetween
> > >
> > >
> > > How to avoid empty entries in drop down box if some of cell from A1:A7
> > > are empty?
> > >
> > > Thanks
> > >
>
>
>

Jim Thomlinson wrote:
> Oops... For the life of me I thought you could use non-contiguious ranges in
> validation arguments... Should have tested it all the way. Give this a try...
>

Thanks everybody.
I could sort data to have empty cells at the end and this formula as a
source works perfect

=OFFSET($A$1,,,COUNTA($A$1:$A$7),1)