Userform - Combo box with Range unexpected results ...

[Rob W]

Greetings,

The following code below finds empty cells in any of the columns and selects
the entire row.
When I run the code and run the range name.select I can see its selected all
the rows with a blank cell value in them.


Private Sub UserForm_Initialize()

Dim patientList As Range
Set patientList =
Sheets("Data").Range("A:A,D,E:E,F:F,G:G").CurrentRegion
Set patientList = patientList.SpecialCells(xlCellTypeBlanks).EntireRow
patientList.Select
Me.comboPatient.RowSource = patientList.Columns(1).Address

End Sub

However when I populate my RowSoure (combo box) its only ever populates one
value (The first row it sees with a blank cell value)?
If i remove the line which find blank cells it lists all the values in
column A rather than the selection I require (any rows with blank values I
want to appear in the combo)

Can anyone please please help me, Im going crazy !!!

Thanks
Rob W

 

[Jim Cone]

When you have a multi area range you must specify each area
or you only get the first area returned.
Looping thru the areas is the common solution.
Here is my test code which seems to work.
Maybe it will be of some help.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
'---

Sub Where()
Dim patientList As Range
Dim rngArea As Range
Dim colList As Collection

Set colList = New Collection
Set patientList = _
Application.Union(Sheets("Data").Range("A:A"), Sheets("Data").Range("D:G"))
Set patientList = _
Application.Intersect(Sheets("Data").UsedRange, patientList)
Set patientList = patientList.SpecialCells(xlCellTypeBlanks)

For Each rngArea In patientList
On Error Resume Next
'Error if a duplicate
colList.Add vbNullString, CStr(rngArea.Row)
If Err.Number = 0 Then
UserForm1.ComboBox1.AddItem "Row " & rngArea.Row
End If
Next
UserForm1.Show
Unload UserForm1
Set colList = Nothing
End Sub
'---



"Rob W"
wrote in message
Greetings,
The following code below finds empty cells in any of the columns and selects
the entire row.
When I run the code and run the range name.select I can see its selected all
the rows with a blank cell value in them.

Private Sub UserForm_Initialize()
Dim patientList As Range
Set patientList =
Sheets("Data").Range("A:A,D,E:E,F:F,G:G").CurrentRegion
Set patientList = patientList.SpecialCells(xlCellTypeBlanks).EntireRow
patientList.Select
Me.comboPatient.RowSource = patientList.Columns(1).Address
End Sub

However when I populate my RowSoure (combo box) its only ever populates one
value (The first row it sees with a blank cell value)?
If i remove the line which find blank cells it lists all the values in
column A rather than the selection I require (any rows with blank values I
want to appear in the combo)
Can anyone please please help me, Im going crazy !!!
Thanks
Rob W

 

[Rob W]

Thanks very useful, Ive only just got round to looking at the reply (UK
based)

I came up with something similar

Dim lRowEnd As Long
Dim R As Range
Dim wsData As Worksheet

Set wsData = Sheets("Data")
lRowEnd = wsData.Cells(Rows.Count, "A").End(xlUp).Row
With comboPatient
.Clear
For Each R In wsData.Range("E1:E" &
lRowEnd).SpecialCells(xlCellTypeBlanks)
.AddItem CStr(wsData.Cells(R.Row, "A").Value)
Next R
End With

I will look into your solution in more detail later, thanks again.
I havent used collections before so it should be interesting ..

Cheers
Rob W