Macro to subsetting tables

kim_malek · Feb 6, 2011

Hi,
I'm new to writing excel macros, so thanks for helping me out.

Here's my problem:

I have a table with different rownames and different columns
My aim is to get a subset of this table by giving the names of variables.
Let's see an example:
on sheet1 i have
<table class="MsoNormalTable" style="width: 244pt; margin-left: 3.5pt; border-collapse: collapse; border: medium none;" border="1" cellpadding="0" cellspacing="0" width="325"> <tbody><tr style="height: 15pt;"> <td style="width: 48.8pt; border: 1pt solid windowtext; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> year
</td> <td style="width: 48.8pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> cars
</td> <td style="width: 48.8pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> dog
</td> <td style="width: 48.8pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> bikes
</td> <td style="width: 48.8pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> motorbikes
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 1960
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

12

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

112

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

45

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

56

</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 1970
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

213

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

12

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

123

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

34

</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 1980
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

213

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

546

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

12

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

13

</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 1990
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

2312

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

234

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

234

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

234

</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 2000
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

1423

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

324

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

123

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

34

</td> </tr> </tbody></table>
I would like that for example i write in an other sheet (or some kind of userform?) 1960, 1980, and i get the following:
<table class="MsoNormalTable" style="width: 253.2pt; margin-left: 3.5pt; border-collapse: collapse; border: medium none;" border="1" cellpadding="0" cellspacing="0" width="338"> <tbody><tr style="height: 15pt;"> <td style="width: 48.8pt; border: 1pt solid windowtext; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> year
</td> <td style="width: 48.8pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> cars
</td> <td style="width: 48.8pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> dog
</td> <td style="width: 48.8pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> bikes
</td> <td style="width: 58pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="77" nowrap="nowrap"> motorbikes
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 1960
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

12

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

112

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

45

</td> <td style="width: 58pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="77" nowrap="nowrap">

56

</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 1980
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

213

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

546

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

12

</td> <td style="width: 58pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="77" nowrap="nowrap">

13

</td> </tr> </tbody></table>
Or an other option should be that both the rownames and the columnnames can be given:
for example on sheet2 i have:
<table class="MsoNormalTable" style="width: 97.6pt; margin-left: 3.5pt; border-collapse: collapse; border: medium none;" border="1" cellpadding="0" cellspacing="0" width="130"> <tbody><tr style="height: 15pt;"> <td style="width: 48.8pt; border: 1pt solid windowtext; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 1960
</td> <td style="width: 48.8pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

cars

</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 1980
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

bikes

</td> </tr> </tbody></table>
and the result should be (presented in an other workbook or worksheet):
<table class="MsoNormalTable" style="width: 146.4pt; margin-left: 3.5pt; border-collapse: collapse; border: medium none;" border="1" cellpadding="0" cellspacing="0" width="195"> <tbody><tr style="height: 15pt;"> <td style="width: 48.8pt; border: 1pt solid windowtext; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> year
</td> <td style="width: 48.8pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> cars
</td> <td style="width: 48.8pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> bikes
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 1960
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

12

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

45

</td> </tr> <tr style="height: 15pt;"> <td style="width: 48.8pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap"> 1980
</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

213

</td> <td style="width: 48.8pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

12

</td> </tr> </tbody></table>
I ’ve writen so far:

Code:

Sub Test()
    Dim Rng As Range
    Dim Sh As Worksheet
    Dim c As Range
    Dim x As Integer
    Dim y As Integer
    Dim i As Integer
    Set Rng = Worksheets("Sheet1").Range("A1:A" & Range("A65536").End(xlUp).Row)
    Set Rngtt = Worksheets("Sheet1").Range("1:" & Range("a1").End(xlToRight).Column)
    Set Sh = Worksheets.Add
    Set Shtt = Worksheets.Add
    x = 1
    y = 1
    
For i = 1 To 2
    For Each c In Rng
        If c.Value = Worksheets("Sheet2").Cells(i, 1) Then
            c.EntireRow.Copy Sh.Cells(x + 1, 1)
            x = x + 1
        End If
    Next c
Next i
   
For Each c In Rngtt
        If c.Value = "cars" Then
            c.EntireColumn.Copy Shtt.Cells(1, y + 1)
            y = y + 1
        End If
    Next c
End Sub

I know it's not working, but I got tired trying.
Thank you for your help.
Kim

Jerry Sullivan · Feb 7, 2011

Hi Kim and Welcome,

You can try the code below. The first part uses AutoFilter to copy the rows you specify to a new sheet. The second part deletes columns that don't meet your critieria.

Rich (BB code):

Sub Test2()
    On Error Resume Next
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        .AutoFilterMode = False
        With .Range("A1").CurrentRegion
            .AutoFilter Field:=1, Criteria1:=Array("1960", "1980"), _
                Operator:=xlFilterValues
            Sheets.Add After:=Sheets("Sheet1")
            .SpecialCells(xlCellTypeVisible).Copy _
                Destination:=ActiveSheet.Range("B2") 'Paste here
            .AutoFilter
        End With
    End With
 
    Dim i As Long
    Dim strHeading As String
    Dim varColtoKeep() As Variant
    varColtoKeep = Array("cars", "bikes")
    With ActiveSheet
        With .Range("B2").CurrentRegion
            For i = .Columns.Count - 1 To 1 Step -1
                strHeading = .Resize(1, 1).Offset(0, i)
                If isMember(strHeading, varColtoKeep) = False Then
                    .Resize(1, 1).Offset(0, i).EntireColumn.Delete
                End If
            Next i
        End With
    End With
    Erase varColtoKeep
End Sub
 
Private Function isMember(strWhat As String, Arr() As Variant) As Boolean
    Dim i As Long
    For i = LBound(Arr) To UBound(Arr)
        If Arr(i) = strWhat Then
            isMember = True
            Exit Function
        End If
    Next i
    isMember = False
End Function

This uses CurrentRegion to grab all the cells in your table, which assumes your table doesn't have any blank rows or columns.

If that does what you want, you could improve it another step as you described using listboxes to specify your criteria instead of having it hardcoded in the macro.

Macro to subsetting tables

kim_malek

New Member

Excel Facts

Jerry Sullivan

MrExcel MVP

Similar threads

Forum statistics

Share this page

Macro to subsetting tables

kim_malek

New Member

Excel Facts

Jerry Sullivan

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock