Update Macro to Filter for Font.Regular Rather than Array

[MRHein]

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!

 

[igold]

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.

 

[Fluff]

You could try

   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

 

[MRHein]

Thanks! That worked!

 

[Fluff]

You're welcome & thanks for the feedback