Selecting specific rows

lshea

New Member
Joined
Mar 9, 2016
Messages
3
I would like to select all rows in a spreadsheet where the entry in column A is blank. These rows I will then format with a different color and bold in one fail swoops.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

starl

Administrator
Joined
Aug 16, 2002
Messages
5,969
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
Code:
Dim rng As Range

On Error Resume Next
Set rng = ActiveSheet.Range("A1").EntireColumn.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
    rng.EntireRow.Font.Bold = True
   'what other formatting you want
End If
 

lshea

New Member
Joined
Mar 9, 2016
Messages
3
Huge learning curve on my part. I read Help on how to see the Developer Tab visible. I copied/pasted your macro code. I searched help on how to select red font.
I'm getting an error that I don't understand. The line "On Error Resume Next" is highlighted and the error message says "Compile error: Invalid outside procedure."
I read something about you have to do something to your file to allows for macros?
 

starl

Administrator
Joined
Aug 16, 2002
Messages
5,969
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
Workbooks have to be xls or xlsm to allow for macro. Then you have to be sure to Enable Macros when you open a workbook with macros.

But your current issue is that I didn't put the code in a procedure.
Copy/paste the following:

Code:
Sub SelectBlanksinColumnA()
'works on the ACTIVE sheet
Dim rng As Range

On Error Resume Next
Set rng = ActiveSheet.Range("A1").EntireColumn.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
    With rng.EntireRow.Font
        .Bold = True
        .Color = -16776961 'red
    End With
End If
End Sub

'If you don't want to color the entire row
'Then change the With above to this
'the 3 meaning the first 3 columns starting at column A
'So columns A:C would be bold and red
'
'With rng.Resize(, 3)
 
Last edited:

starl

Administrator
Joined
Aug 16, 2002
Messages
5,969
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
clarification - if you don't want the entire row, use the following to select # of columns (including column A)

Code:
With rng.Resize(, 3).Font

it would replace the original With statement
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,566
Members
414,078
Latest member
Frills

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
Top