Hi Ashly

Start with creating a combobox in the spreadsheet

Put the following 2 procedures in a standard module:

Note where ever I use the Cells property change the 2nd argument as
necessary for example say you want to filter by Column E, then edit like
this: .Cells(2, 5) and .Cells(LastRow, 5)
also rename the sheet as your sheet name
-------------------------------------------
Sub LoadCombo()
Dim Sht As Worksheet, Rng As Range, DataRng As Range
Dim LastRow As Long, i As Long
Dim Cb, Data()

Set Sht = ThisWorkbook.Worksheets("Sheet1")

With Sht
Set Cb = Sheet1.ComboBox1
Cb.Clear
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
ReDim Data(2 To LastRow - 1)
Set DataRng = .Range(.Cells(2, 1), .Cells(LastRow, 1))

For i = 2 To LastRow - 1
Data(i) = DataRng.Cells(i - 1)
Next i

SortData Data

Cb.AddItem Data(LBound(Data))
For i = LBound(Data) + 1 To UBound(Data)
Debug.Print Data(i)
If Data(i) < Data(i - 1) Then Cb.AddItem Data(i)
Next i
End With
End Sub
--------------------------------------------------------------------------
Sub SortData(List())
Dim First As Integer, Last As Long
Dim i As Long, x As Long
Dim Temp

First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For x = i + 1 To Last
If List(i) List(x) Then
Temp = List(x)
List(x) = List(i)
List(i) = Temp
End If
Next x
Next i
End Sub
-------------------------------------------------------
Go in Design Mode in the sheet containing the combo and double click the combo

and put the following code:

Private Sub ComboBox1_Change()
Dim Sht As Worksheet, Cb
Dim LastRow As Long, i As Long
Dim DataRng As Range

Set Cb = Me.ComboBox1
Set Sht = ThisWorkbook.Worksheets("Sheet1")

With Sht
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set DataRng = .Range(.Cells(1, 1), .Cells(LastRow, 1))

If Cb.Text = "" Then
DataRng.AutoFilter Field:=1
Else
DataRng.AutoFilter Field:=1, Criteria1:=Cb.Text
End If
End With
End Sub

Hope this helps let me know if i can be of any further assistance
--
A. Ch. Eirinberg


"Ashly" wrote:

Hi

Been racking my brains for a few days but simply seem to have blanked
out. I have an excel file with the following headers.

Product|Version|Status|Date|Executive|Company Name|Contact|


Note: All the rows under the headers can be repeated multiple times.
Filter is an obvious method to get data fulfilling a criteria. But I
would like to avoid use of "Filter"

Is there any method wherein I can create a combo box on "Company Name"
and get a list of all corresponding entries under the "Company Name"
thus selected from the combo box. Further, is it possible to get only
a unique list in the combo box but on selection, display all the rows
and columns with the matching "Company Name"? This, is possible, would
have to be done in a new sheet. Its ok with me as long as the combo
box and display properties gets updated every time I update the list
in the main worksheet.

By the way, I am using Excel 2007. Any Help in this is appreciated.


Thanks


Ashish Pradhan