Autofilter Based on Cell Format

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
101
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?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
101
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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

Board Regular
Joined
Sep 23, 2006
Messages
202
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:
Code:
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

Board Regular
Joined
Dec 17, 2004
Messages
101
You guys are a real lifesaver. Both of these will do what I need to do. I really appreciate it.
 

Forum statistics

Threads
1,141,098
Messages
5,704,319
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top