Slight adjustment to my macro [VBA]

[Martin_H]

Hi team,

basically before runing my macro (see below) I want to check if there is any number in the range X4:X126.

If a number exists in the mentioned range, the macro will continue as is.

However, if there is no number in the mentioned range, I want to stop the macro and give the message "There is no number".

Thank you for your help. Much appreciated.

Public Sub PRIO(ByRef control As Office.IRibbonControl)
Application.ScreenUpdating = False
Application.EnableEvents = False
With Range("X4:X126").SpecialCells(xlCellTypeBlanks)
If .EntireRow.Hidden = True Then
.EntireRow.Hidden = False
With Range("X3").Font
.Color = vbWhite: .Bold = False
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
Else
.EntireRow.Hidden = True
With Range("X3").Font
.Color = vbGreen: .Bold = True
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
End If
End With
End Sub

 

[Joe4]

Add this line near the top:

    If Application.WorksheetFunction.Count(Range("X4:X126")) = 0 Then Exit Sub

 

[Fluff]

Do you need to specifically test for a number, or just any non-blank cells?

 

[Martin_H]

Add this line near the top:

    If Application.WorksheetFunction.Count(Range("X4:X126")) = 0 Then Exit Sub

Thank you Joe.

 

[Martin_H]

Do you need to specifically test for a number, or just any non-blank cells?

Yes sir, just a number.

 

[Fluff]

Joe's already supplied the answer, so you should be good to go.

 

[Joe4]

You are welcome.
Glad we were able to help!