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


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

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


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:=xlBetw een


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" wrote in
message ...
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:=xlBetw een


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" wrote in
message ...
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:=xlBetw een


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)