VBA if a row is all blank in a range of columns, highlight the blanks and the headers

[AnnAnn]

Hello,
This code works but takes 11 seconds to run on a 500 row sheet; it's part of a large main macro and I need it to run faster. I've searched Google, YouTube, and other threads on this site but can't find what I need.
Dim lastRow As Long, lastCol As Long, i As Long
Dim col As Variant
Dim headerCell As Range

lastCol = last_col(ws)
lastRow = Last_Row_For_Realsies(ws, lastCol)

Dim checkColumns As Variant
checkColumns = Array("A", "B", "C", "D", "E", "F", "G", "H", "K", "M", "P", "Q", "S", "U", "W", "Y", "AA")

For i = 3 To lastRow
Dim allBlank As Boolean

allBlank = True


For Each col In checkColumns
If ws.Cells(i, col).Value <> "" Then
allBlank = False
Exit For
End If
Next col

If allBlank Then

For Each col In checkColumns
ws.Cells(i, col).Interior.Color = RGB(255, 204, 0)
Set headerCell = ws.Cells(2, col)
headerCell.Interior.Color = RGB(0, 0, 0)
headerCell.Font.Color = RGB(255, 255, 255)
Next col
End If

Next i

 

[AnnAnn]

Is the code you posted in post #1 a 'partial' of the main macro ?

Yes. This is one of 74 subs on the main macro.

Public Sub NewMainMacro()


Dim rosterWB As Workbook
Dim ws As Worksheet

Set rosterWB = FindExcel.GetRoster()

If Not rosterWB Is Nothing Then

    Set ws = rosterWB.Worksheets(1)
    
End If

ws.AutoFilterMode = False

Call Sort(ws)
Call ServiceAddress1(ws)
Call Add1NoNbr(ws)
Call ServiceAddress2(ws)
Call ServiceZipCode(ws)
Call ServiceCityNbr(ws)
Call PhoneNbr(ws)
Call ServiceCounty(ws)
Call ServiceStLength(ws)
Call NPI(ws)
Call TaxID(ws)
Call NewADACols(ws)
Call CompAllNbrs(ws, "J") 'TNF
Call CompAllNbrs(ws, "L") 'PMC
Call CompAllNbrs(ws, "O") 'DUE
Call TeleHealth(ws)
Call TeleHealthCUisEmpty(ws)
Call TeleHealthBlank(ws)
Call FirstLastNameHasNbr(ws)
Call MidNameNbr(ws)
Call TaxIDFillDown(ws)
Call License(ws)
Call Language(ws)
Call dob(ws)
Call TNFPFIN(ws)
Call DUEPFIN(ws)
Call HMO(ws)
Call BHD(ws)
Call BAV(ws)
Call BFC(ws)
Call MCA(ws)
Call ADV(ws)
Call PMC(ws)
Call BillNPI(ws)
Call PFIN(ws, "A") 'PPO
Call PFIN(ws, "B") 'BDS
Call PFIN(ws, "C") 'EDS
Call PFIN(ws, "D") 'BCS
Call PFIN(ws, "E") 'BCO
Call PFIN(ws, "F") 'BCE
Call PFIN(ws, "G") 'PTW
Call PFIN(ws, "H") 'TNF
Call PFIN(ws, "K") 'PMC
Call PFIN(ws, "M") 'DUE
Call PFIN(ws, "P") 'HPN
Call PFIN(ws, "Q") 'HMO
Call PFIN(ws, "S") 'ADV
Call PFIN(ws, "U") 'BHD
Call PFIN(ws, "W") 'BAV
Call PFIN(ws, "Y") 'BFC
Call PFIN(ws, "AA") 'MCA
Call Gender(ws)
Call DiffGender(ws)
Call DiffProvRole(ws)
Call DiffProvType(ws)
Call DiffSpecialty(ws)
Call DiffDOB(ws)
Call DiffNPI(ws)
Call DiffTaxID(ws)
Call DiffLicense(ws)
Call DiffSubSpec(ws)
Call DiffTitle(ws)
Call SameNPI(ws)
Call SameTaxID(ws)
Call Specialty(ws)
Call SameLicense(ws)
Call ProvRole(ws)
Call ProvType(ws)
Call ProvSpec(ws)
Call IndNPIvsBillNPI(ws)
Call AllBlankPFINs(ws)
Call Title(ws)
Call EffDate(ws)
Call FindDupRows(ws)
Call EducationColumns(ws)
Call OfficeHours(ws)


ws.Range("A2:EL2").AutoFilter
    

Msgbox ("The macro has completed its run.")

End Sub

 

[mumps]

So is the code I suggested working properly now?

 

[AnnAnn]

So is the code I suggested working properly now?

 

[mumps]

I tested the code on some dummy data and it worked properly. If it is not working properly, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[AnnAnn]

So is the code I suggested working properly now?

No. I received run-time error 1004 at: If WorksheetFunction.CountA(Intersect(ws.Rows(i), ws.Range("A:H,K:K,M:M,P:Q,S:S,U:U,W:W,Y:Y,AA:AA"))) <> 17 Then

 

[mumps]

See Post #14.

 

[AnnAnn]

I tested the code on some dummy data and it worked properly. If it is not working properly, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

What do you mean by a screenshot of my sheet? Do you mean the entire macro? If you mean the sheet that has my data, I can't do that because it's sensitive data.
On a side note, I have tried using Intersect before and I always get the runtime 1004 error.

 

[mumps]

I can't do that because it's sensitive data.

You could replace the sensitive data with generic data. I would only need about a dozen or so rows of your data.

 

[AnnAnn]

What do you mean by a screenshot of my sheet? Do you mean the entire macro? If you mean the sheet that has my data, I can't do that because it's sensitive data.
On a side note, I have tried using Intersect before and I always get the runtime 1004 error.

 

[AnnAnn]

You could replace the sensitive data with generic data. I would only need about a dozen or so rows of your data.

Thank you, I should have thought of that before I replied. I hope this is what you need.