Autofilter Based on Cell Format

[madkinson]

Is it possible to write VBA that will Autofilter based on the format of a cell? What I have a a large dataset where subtotals are in bold. What I want to do is to Autofilter on the bold.

A sample piece of the data:
193 - 0 - 24000 - 5000 - 3500
193 - 0 - 5000 - 0 - 0
193 - 1 - 4000 - 5000 - 3000

I subtotal the data based on the change in the second column. Excel places the text "0 Total" in bold in column 2.
193 - 0 - 24000 - 5000 - 3500
193 - 0 - 5000 - 0 - 0
- 0 Total -
193 - 1 - 4000 - 5000 - 3000

I tried to do a search based on finding the string "Total" and that bombed. Any ideas?

 

[madkinson]

I figured it out. I can do the following:

Set MyRange = Range("B1:B" & Range("B65536").End(xlUp).Row)
For Each c In MyRange
If Len(c.Value) < 3 Then c.EntireRow.Hidden = True
Next c

and not have to worry about the format. Since the values in that column are either 1 or 2 characters long except for the subtotal, this hides the rows that are not needed.

 

[Boller]

Instead of looping through each row you should be able to show the sub-totals only with :-

ActiveSheet.Outline.ShowLevels RowLevels:=2


To show all levels :-

ActiveSheet.Outline.ShowLevels RowLevels:=3

 

[Erdinç E. Karaçam]

First scene of the table:

<table border=1><tr><td align=left valign=center><font size=1 color=red face=verdana>Gönderen XL Ver.:</font><font size=1 face=verdana> Office 2003 / </font><font size=1 color=red face=verdana>OS Ver.:</font><font size=1 face=verdana> Windows XP </font></td></tr><tr valign=top><td><table border=0 bgcolor=d4d0c8 cellspacing=1 cellpadding=1 align=center><tr align=center valign=center bgcolor=white><td bgcolor=d4d0c8 align=center><font color=black size=2>+</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>A</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>B</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>1</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Column 1</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Column 2</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>2</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>0</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>3</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>0</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>4</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>4</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>5</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>0</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>6</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>3</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>7</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>21</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>8</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>43</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>9</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>65</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>10</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>45</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>11</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>34</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>12</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>5</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>13</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>0</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>14</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>3</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>15</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>76</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>16</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>0</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>17</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>232</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>18</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>565</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>19</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>2</font></td></tr></table></td></tr><tr><td bgcolor=f0f8ff align=center><font color=black size=1 face=verdana>XLtoHTML v1.1 / ExcelTürkiye - 2006©</font></td></tr></table>

After code is running:

<table border=1><tr><td align=left valign=center><font size=1 color=red face=verdana>Gönderen XL Ver.:</font><font size=1 face=verdana> Office 2003 / </font><font size=1 color=red face=verdana>OS Ver.:</font><font size=1 face=verdana> Windows XP </font></td></tr><tr valign=top><td><table border=0 bgcolor=d4d0c8 cellspacing=1 cellpadding=1 align=center><tr align=center valign=center bgcolor=white><td bgcolor=d4d0c8 align=center><font color=black size=2>+</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>A</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>B</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>1</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Column 1</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Column 2</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>2</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>0</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>3</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>0</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>4</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>4</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>5</font></td><td bgcolor=white></td><td bgcolor=yellow nowrap=true><font size=1 color=black face=verdana>Total: 4</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>6</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>0</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>7</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>3</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>8</font></td><td bgcolor=white></td><td bgcolor=yellow nowrap=true><font size=1 color=black face=verdana>Total: 3</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>9</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>21</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>10</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>43</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>11</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>65</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>12</font></td><td bgcolor=white></td><td bgcolor=yellow nowrap=true><font size=1 color=black face=verdana>Total: 129</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>13</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>45</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>14</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>34</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>15</font></td><td bgcolor=white></td><td bgcolor=yellow nowrap=true><font size=1 color=black face=verdana>Total: 79</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>16</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>5</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>17</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>0</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>18</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>3</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>19</font></td><td bgcolor=white></td><td bgcolor=yellow nowrap=true><font size=1 color=black face=verdana>Total: 8</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>20</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>76</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>21</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>0</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>22</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>232</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>23</font></td><td bgcolor=white></td><td bgcolor=yellow nowrap=true><font size=1 color=black face=verdana>Total: 308</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>24</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>565</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>25</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>193</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>2</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>26</font></td><td bgcolor=white></td><td bgcolor=yellow nowrap=true><font size=1 color=black face=verdana>Total: 567</font></td></tr></table></td></tr><tr><td bgcolor=f0f8ff align=center><font color=black size=1 face=verdana>XLtoHTML v1.1 / ExcelTürkiye - 2006©</font></td></tr></table>


Codes:

Sub SumUnderBoldCell()
    Dim Cel As Range
    Dim UpNo As Long
    Dim DownNo As Long
    Dim Tirnak As String
    Dim Parantez As String
    
    Parantez = ")"
    Tirnak = """"
    Tirnak = Mid(Tirnak, 1, 1)
        For Each Cel In Range("B2:B" & Range("B:B").SpecialCells(xlCellTypeLastCell).Row)
            If Cel.Font.Bold = False And Cel <> "" Then UpNo = Cel.Row
            If Cel.Font.Bold = True Then
                DownNo = Cel.Row
                Rows(Cel.Row + 1).Insert Shift:=xlDown
                Rows(Cel.Row + 1).Font.Bold = False
                Rows(Cel.Row + 2).Insert Shift:=xlDown
                Rows(Cel.Row + 2).Font.Bold = False
                Cel.Offset(1, 0).Value = "=" & "SUM(B" & UpNo - 1 & ":B" & DownNo & Parantez
                Cel.Offset(1, 0).Value = "Total: " & Cel.Offset(1, 0).Value
                Cel.Offset(1, 0).Interior.ColorIndex = 6
            End If
        Next Cel
    
        For Each Cel In Range("B2:B" & Range("B:B").SpecialCells(xlCellTypeLastCell).Row)
            If Cel = "" Then Rows(Cel.Row).Delete
        Next Cel
End Sub

Do you want to do this? or is there a missunderstanding?

 

[madkinson]

You guys are a real lifesaver. Both of these will do what I need to do. I really appreciate it.

 

[Erdinç E. Karaçam]

Not at all madkinson, take care.