Autofilter Based on Cell Format

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
113
Office Version
  1. 365
Platform
  1. Windows
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
You guys are a real lifesaver. Both of these will do what I need to do. I really appreciate it.
 
Upvote 0

Forum statistics

Threads
1,212,096
Messages
6,105,942
Members
447,985
Latest member
gigi11

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
Back
Top