Update Macro to Filter for Font.Regular Rather than Array

MRHein

New Member
Joined
Jul 12, 2019
Messages
3
I was recently asked to modify an existing macro (written by a former employee) returning rows from an Array in column 4 to return only the rows in column 4 that are not blank and in Font.Regular
This macro compiles data from numerous reports into an overall monthly report.The number of rows vary between various workbooks, and there are a variable number of blank rows in the subject column. The (working) macro looks like this:
With mybook.Worksheets(3)
.AutoFilterMode = False
lRow = .Range("I" & .Rows.Count).End(xlUp).Row
.Range("$A$14:$BA$" & lRow).AutoFilter Field:=4, Criteria1:=Array( _
"FEE ON LABOR", "FEE ON OTHER DIRECT COSTS", "NON-BILLABLE COST", _
"CAPPED INDIRECTS"), Operator:=xlFilterValues

My (non-functional) macro looks like this:
With mybook.Worksheets(3)
.AutoFilterMode = False
lRow = .Range("I" & .Rows.Count).End(xlUp).Row
.Range("$A$14:$BA$" & lRow).AutoFilter Field:=4, Criteria1:=_"Font.Regular",
Operator:=xlFilterValues

Help Please!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Hi MRHein,

Welcome to the MrExcel Forum.

I have poked around a bit and I don't think it can be done. At the very least, I think you would have to use the Font.FontStyle property to return the word "Regular". I tried a couple of things but could get it to work. Perhaps one of the more knowledgeable forum members will chime in with a solution.
 
Upvote 0
You could try
Code:
   With mybook.Worksheets(3)
      .AutoFilterMode = False
      lrow = .Range("I" & .Rows.Count).End(xlUp).Row
      For i = 15 To lrow
         .Cells(i, "BB").Value = .Cells(i, 4).Font.FontStyle = "Regular"
      Next i
      .Range("A14:BB" & lrow).AutoFilter 4, "<>"
      .Range("A14:BB" & lrow).AutoFilter 54, True
   End With
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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