Forum Discussion
MAngosto
Feb 14, 2024Iron Contributor
Filter a range of cells with headers and data based on a criteria and construct a Table (VBA)
Hello experts, I am running Microsoft® Excel® for Microsoft 365 on Windows 10. Please kindly refer to the sample images attached below for a better understanding of the context. The first sheet d...
- Feb 14, 2024
Try this:
Sub FilteredTable() Dim sw As Worksheet Dim tw As Worksheet Dim sr As Long Dim m As Long Dim sc As Long Dim n As Long Dim tr As Long Dim tc As Long Dim rg As Range Dim f As Boolean Application.ScreenUpdating = False Set sw = ActiveSheet m = sw.Range("B4").End(xlDown).Row n = sw.Range("C3").End(xlToRight).Column Set rg = sw.Range("O3").CurrentRegion Set tw = Worksheets.Add(After:=sw) tw.Range("B2").Value = "Product" tr = 2 For sr = 4 To m If Application.VLookup(sw.Cells(sr, 2).Value, rg, 2, False) = "True" Then tr = tr + 1 tw.Cells(tr, 2).Value = sw.Cells(sr, 2).Value tc = 2 For sc = 3 To n If Application.VLookup(sw.Cells(3, sc), rg, 2, False) = "True" Then tc = tc + 1 If Not f Then tw.Cells(2, tc).Value = sw.Cells(3, sc).Value If sc = n Then f = True End If End If sw.Cells(sr, sc).Copy Destination:=tw.Cells(tr, tc) End If Next sc End If Next sr tw.ListObjects.Add Source:=tw.Range("B2").CurrentRegion, XlListObjectHasHeaders:=xlYes Application.ScreenUpdating = True End Sub
peiyezhu
Feb 14, 2024Bronze Contributor
Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0;';Data Source=" & ThisWorkbook.FullName
sql="select * from [Sheet1$]"
fields="Product," & "Spain,Italy"
'here use function textjoin to get fields and products true e.g. Spain,Italy and Apple,Orange ;the source sheet data starts at range a1
criteria=" where instr(""Apple,Orange"",Product)>0"
sql="select " & fields & " from [Sheet1$]" & criteria
Set Rst=CreateObject("ADODB.Recordset")
Rst.CursorLocation = 3
Rst.open sql,conn,adOpenKeyset,3
ActiveCell.CopyFromRecordset Rst
MAngosto
Feb 14, 2024Iron Contributor
Hello!
Thank you for your kind reply. I do not doubt it could be useful.
Unfortunately, I am not familiarized with ADO at all, and I am afraid I will not even get to understand your code. Do you have, by any chance, a purely VBA version of it?
Thanks again.
Thank you for your kind reply. I do not doubt it could be useful.
Unfortunately, I am not familiarized with ADO at all, and I am afraid I will not even get to understand your code. Do you have, by any chance, a purely VBA version of it?
Thanks again.
- peiyezhuFeb 14, 2024Bronze Contributorpurely VBA version of it?
Yes,this is pure vba.
Have you run it?