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

AnnAnn

New Member
Joined
Mar 26, 2024
Messages
36
Office Version
  1. 2016
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
 
I don't see where ws was declared where the posted code contains the ws reference.
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I don't see where ws was declared where the posted code contains the ws reference.
It's declared in the title of the Sub, please see below.
Sub AllBlankPFINs(ws As Worksheet)

When I call it in the main sub, I use:
Call Sub AllBlankPFINs(ws)

All of my other subs work when I use both of these, except when Union or Intersect is in the code.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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