How to display only a "select" amount of data in a table

[Vlado Baban]

I have a hard problem. I have a EXCEL table with 6,000 lines that looks like this:
CELL1......CELL2
ProductA 4
ProductA -1
ProductB 1
ProductB 2
ProductC -3
ProductC 1
So...I want to display only a "select" amount of data. ie. Product B does NOT have any negative values, and I want to REMOVE Product B from the table. I want to keep Product A, and Product C in the table, because they have one (or more) lines that show a negative value. The hard part (for me) is that I STILL want the table to show the other positive values for Products A & B. So the final table should look like this:
ProductA 4
ProductA -1
ProductC -3
ProductC 1
How do I do this? I have struggled to find a function that is intuitive enough to capture what I am trying to do....?

 

[Dartagnan]

Just to clarify a few things

Is your "table" in a worksheet? Or do you have it in a database?

Where do you want the new data or table to be displayed?

What version of Excel are you using?

 

[Vlado Baban]

My table is just in a Excel worksheet,
Also, I can use both Excel 2003, and 2007,
And, I would like to display the "selected" data in a different table and/or different tab, in the same Excel file (but am open to other options)

 

[Dartagnan]

Believe this code should do the trick.


Sub Excel_QueryTable()
'On Error Resume Next

Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String
Dim ws As Worksheet
Dim qt As QueryTable

'Set ws = Sheets("No Product B")

If Not WorksheetExists("NoProductB") Then
Worksheets.Add.Name = "NoProductB"
Else
Sheets("NoProductB").Delete
Worksheets.Add.Name = "NoProductB"
End If

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\book1.xlsm;Extended Properties=Excel 8.0;Persist Security Info=False"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open

SQL = "Select * from [Sheet2$] where Product <> 'ProductB'"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

Set qt = Worksheets("NoProductB").QueryTables.Add(Connection:=oRS, _
Destination:=Sheets("NoProductB").Range("a1"))

qt.Refresh

If oRS.State <> adStateClosed Then
oRS.Close
End If


If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing

End Sub

____________________________________________________

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function