Eliminating bland from Data Validation list?

[robs3131]

Hi,

I looked around other postings but could not find the answer -- what I'm
having a problem with is thta in my data validation list, I have a range set
(which refers to column H) -- the number within the range can change, and I
sort it every time it does change so that there are no spaces between values.


The problem is that there are numerous blanks in the list at bottom, after
the last actual value in the range. Is there any way to get rid of those
blanks? I tried deleting (delete shift up) all blank cells after the last
value and through the last cell (H65536), but this doesn't help. Below is my
code. Thanks!


With .Range("B2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=dvrange"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Please select value"
.InputMessage = ""
.ErrorMessage = "Please select a value from the drop down menu."
.ShowInput = True
.ShowError = True
End With

 

[robs3131]

I figured out a solution - don't know if it's the best solution, but it is
working. If there is a better solution you know of, I'd be interested to see
what it is.

What I did was definied a range within the workbook (by highighting the
initial range of cells I want to use in the validation list and then naming
the range "dvrange" by typing this into the upper right corner, just to the
left of the formula bar) and then added the code below to redefine the range
when needed:

With Sheets("Invoices not tied")
ActiveWorkbook.Names.Add Name:="dvrange", RefersToR1C1:=.Range("H1",
..Range("H65536").End(xlUp))
End With

 

[Peter T]

Hi Robert,

If you mean exclude blanks from the bottom of the list, and there are no
other blanks within the list, perhaps a dynamic range.

=OFFSET(dvrange,0,0,COUNTA(dvrange))

Maintain 'dvrange' at least as tall as your potential list.

Regards,
Peter T