Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to filter cells by bold characters in Excel?

You can apply the bold font style to format the data or contents in cells in Excel, but do you know how to filter cells by the cells which containing bold font style in Excel? This article collects several tricky VBA macros to filter cells by bold characters in Excel.

Filter cells by bold character with VBA Macro 1

Filter cells by bold character with VBA Macro 2

Filter cells by bold character with VBA Macro 3


Easily select all bold cells at once in a range in Excel

Click Kutools > Select > Select Cells with Format. The Kutools for Excel's Select Cells with Format utility will help you select all cells with same formatting or color in a range at once.

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Read More      Download the free trial now


Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial


Filter cells by bold character with VBA Macro 1


For example, you need to filter all bold cells in a range as below screenshot shown, please do as follows:

If you can identify whether the strings are bold or not, task goes easy to filer the cells by bold formatting. This VBA Macro is to identify the bod strings in cells.

1: Press the Ctrl + F11 keys together to open the Microsoft Visual Basic for Application window.

2: In the Microsoft Visual Basic for Application window, click Insert > Module, then copy and paste the following VBA code on the new Module window: See screenshot:

VBA code: Filter cells by bold characters

Function IsBold(rCell As Range)
IsBold = rCell.Font.Bold
End Function

3: Select a blank cell adjacent to the corresponding cell of the column you will filter, enter formula =IsBold(B2) into the Formula Bar, and then press the Enter key. If the corresponding cell string are bold, it returns True, otherwise False.

4: Keep selecting the first result cell, and drag the Fill Handle to get all results. See screenshot:

5: Select the header cell of the result column, click Filter under Data tab. Click the arrow button besides the header cell, then only check the TRUE box, and then click the OK button in the drop-down menu.

Now all bold cells in column B are filtered out as below screenshot shown:


Filter cells by bold character with VBA Macro 2

This VBA Macro allows you to filter the bold cells in one column directly.

1: Select the range you will filter all bold cells. In this case we select the range of B2:B17.

Note: You cannot select the header cell at the top of filtered column, says Cell B1.

2: Press the Ctrl + F11 keys together to open the Microsoft Visual Basic for Application window.

3: In the Microsoft Visual Basic for Application window, click Insert > Module, then copy and paste the following VBA code on the new Module window: See screenshot:

VBA code: Filter cells by bold characters

Sub FilterBold()
Dim cell As Range
For Each cell In Selection
If cell.Font.Bold = False Then
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

4: Press the F5 key to run this macro. Then all bold cells are filtered out in specified column immediately as below screenshot shown.


Filter cells by bold character with VBA Macro 3

This VBA macro also allows you to filter all bold cells in specified column directly.

1: Press the Ctrl + F11 keys together to open the Microsoft Visual Basic for Application window.

2: In the Microsoft Visual Basic for Application window, click Insert > Module, then copy and paste the following VBA code on the new Module window: See screenshot:

VBA code: Filter cells by bold characters

Sub FilterBold()
    Dim xRg As Range, xCell As Range
    Dim xAddress As String
    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select a range:", "Kutools for Excel", xAddress, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    For Each xCell In xRg
        If Not xCell.Font.Bold Then xCell.EntireRow.Hidden = True
    Next
    Application.ScreenUpdating = True
End Sub

3: Press the F5 key to run this macro. In the popping up Kutools for Excel dialog box, please select the filter range, says B1:B17 in this case, and then click the OK button.

Now all bold cells are filtered out as below screenshot shown.


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  • Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  • Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  • No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  • Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    richard skins · 2 years ago
    I've used this technique and had an issue when trying to copy the bold data. It only copies some of the data for some reason.

    Is there any way of copying only the bold data using this technique?

    Thanks,

    Richard
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear richard skins,



      We didn't encounter the problem as you mentioned. Would you please provide a screenshot with details of your operation?



      Thank you for your comment!
  • To post as a guest, your comment is unpublished.
    richard skins · 2 years ago
    Hi,

    I'm a complete excel novice, i've done the following technique but now need to copy the data which is bold/true.

    When I have tried to do this is won't copy what is highlighted.

    Any ideas on how I can copy the data?

    Thanks,

    Richard
    • To post as a guest, your comment is unpublished.
      MUHAMMAD FAIZAN · 2 years ago
      @RICHARD

      Can u please provide any screenshot of your spreadsheet showing what u are exactly trying to do?
  • To post as a guest, your comment is unpublished.
    Star Lay · 2 years ago
    Thanks for sharing. It works wonderful.
  • To post as a guest, your comment is unpublished.
    Nitin Jain · 2 years ago
    Hi I am using this code and its working fine but whenever I click on cancel the dialogue box a popup window appear and shows some error. Can you please tell me how to remove that error.




    :Sub FilterBold()
    Dim myRange As Range
    Set myRange = Application.InputBox(Prompt:="Please Select a Range", Title:="InputBox Method", Type:=8)
    myRange.Select
    Application.ScreenUpdating = False
    For Each myRange In Selection
    If myRange.Font.Bold = False Then
    myRange.EntireRow.Hidden = True
    End If
    Next myRange
    Application.ScreenUpdating = True
    End Sub
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Nitin Jain,

      Sorry to reply so late!

      We have found the proble and fixed it already. The VBA script in the article is now updated,

      Thank you for your comment!
  • To post as a guest, your comment is unpublished.
    Nicholas · 4 years ago
    Simple and brilliant, thank you!