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.



arrow blue right bubbleVBA Macro 1:


For example, to filter cells according to the cells containing bold font style:

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.

Step 1: Hold down the Ctrl + F11 keys to open the Microsoft Visual Basic for Application window.

Step 2: Click the Insert >> Module, and paste the following VBA code on the new Module window:

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

Step 3: In the Cell D2, enter the formula of =IsBold(B2), and press the Enter key. If the string in Cell B1 are bold, it returns True in Cell D2, otherwise False.

Step 4: Select the range of D2:D17, and Click the Home >> Fill >> Down to copy the formula to other cells in the range.

Step 5: Click the Filter button under Data tab.

Step 6: Click the arrow button besides the title of Column D.

Step 7: Uncheck the Select All item, and check the True item. See the following screen shot:

Now it filters the cells in column B which are bold, and hide others, see the following screen shot:


arrow blue right bubbleVBA Macro 2:

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

Step 1: Select the range that you will filter by bold. In this case select the range of B2:B17.

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

Step 2: Hold down the Ctrl + F11 keys to open the Microsoft Visual Basic for Application window.

Step 3: Click the Insert >> Module, and paste the following VBA code on the new Module window:

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

Step 4: Press the F5 key to run this macro.

Now you will get the filter result same as the figure we showed you in VBA Macro 1.


arrow blue right bubbleVBA Macro 3:

This VBA macro also allows you to filter column by bold directly.

Step 1: Hold down the Ctrl + F11 keys to open the Microsoft Visual Basic for Application window.

Step 2: Click the Insert >> Module, and paste the following VBA code on the new Module window:

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

Step 3: Press the F5 key to run this macro.

Step 4: InputBox Method dialog box pops up, and please specify the filter range, says B1:B17 in this case, and click OK.

Now it hides all rows whose cells in Column B are not bold, and filter only the cells are bold. See the filtering result.

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink 0 cadafi
I presume that column A is the reference Column with Bold cells.
I create a Name "Bold?" with the refer to as below

=GET.CELL(20,OFFSET(INDIRECT("A1"),ROW()-1,0))

In column B, I type:
=Bold?

Copy down the formula and then use autofilter to filter the value TRUE. DONE
2013-12-20 04:10 Reply Reply with quote Quote
Permalink 0 Anjali
@Cadafi.... thats brilliant... wondering how it works though!
2014-01-30 07:06 Reply Reply with quote Quote
Permalink 0 Mohammad Faizan
@cadafi
ur formula works fine but it is not the optimised or efficient use of it.
Rather we should use direct reference of the range or a cell in place of that complete offset command.
Like the one as follows:

=GET.CELL(20,'Sheet1'!A1)

Copy down the formula and then use autofilter to filter the value TRUE. DONE
2014-02-13 07:05 Reply Reply with quote Quote
Permalink -1 cadafi
@Mohammad Faizan
You are entirely correct. Thanks.
2014-06-25 07:11 Reply Reply with quote Quote
Permalink -1 cadafi
Thanks Mohamand Faizan. You are entirely correct.
2014-06-25 07:26 Reply Reply with quote Quote
Permalink +1 Statistics Guru
This worked perfectly, thank you!!! :D :lol:
2014-06-24 14:38 Reply Reply with quote Quote
Permalink 0 Nicholas
Simple and brilliant, thank you!
2015-05-25 17:28 Reply Reply with quote Quote
Permalink 0 Nitin Jain
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.Inp utBox(Prompt:=" Please Select a Range", Title:="InputBo x Method", Type:=8)
myRange.Select
Application.ScreenUpdating = False
For Each myRange In Selection
If myRange.Font.Bo ld = False Then
myRange.EntireRow.Hidden = True
End If
Next myRange
Application.ScreenUpdating = True
End Sub
2016-07-27 17:30 Reply Reply with quote Quote
Permalink 0 Star Lay
Thanks for sharing. It works wonderful.
2016-08-25 04:28 Reply Reply with quote Quote
Permalink 0 richard skins
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
2016-10-05 11:13 Reply Reply with quote Quote
Permalink 0 MUHAMMAD FAIZAN
@RICHARD

Can u please provide any screenshot of your spreadsheet showing what u are exactly trying to do?
2016-10-11 09:40 Reply Reply with quote Quote
Permalink 0 richard skins
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
2016-10-05 11:20 Reply Reply with quote Quote

Add comment


Security code
Refresh