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!
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,835
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,835
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,661
Messages
5,488,163
Members
407,628
Latest member
Faceless Judge

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top