Solved: Checking for empty cells.

[Marcster]

Hello People,

I have a sub below which doesn't quite do what I want:


Sub CheckColumnC()
If WorksheetFunction.CountA(Range("C2:C500")) = 0 Then
MsgBox "All cells are empty"
Exit Sub
End If
End Sub
This checks to see if all the cells in the range are empty,
and if it is then displays a message box.

I want it to check the cells C2:C500 and display a message box
saying what cells are empty. But not show a message box if
the contents of the cells is not empty.


Thanks,

Marcster.

[Bob Phillips]

It does what you say, no message unless they are all empty.

Are you sure you don't have some cells with formulae or spaces?

[Marcster]

Hi XLD,

In the range C2:C500 some cells will be empty and some
will have a date in.

I'm after a sub which which checks every cell in the range
and display a message box saying what cells are empty.
Any cells in the range which are not empty is to be ignored.

HTH,

Marcster.

[Norie]

The only way I think you can do this would be with a loop.


Sub CheckColumnC()
Dim c As Range
Dim strMsg As String
For Each c In Range("C2:C500")
If IsDate(c) Then
strMsg = strMsg & c.Address & vbCrLf
End If
Next c

If Len(strMsg) > 0 Then
MsgBox "The following cells have dates:" & vbCrLf & strMsg
Else
MsgBox "All cells are empty"
End If
End Sub

[Marcster]

I've come up with this:

Sub CheckColumnC()
If WorksheetFunction.CountBlank(Range("C2:C500")) > 0 Then
MsgBox "1 or more cells is empty."
Exit Sub
End If
End Sub


How do I modify it so it displays 1 message saying what cells are the ones
that are empty?.

Marcster.

[johnske]

Sub WotAboutThis()
MsgBox Cells.SpecialCells(xlCellTypeBlanks).Address
End Sub

[Marcster]

I've got this:

Sub CheckColumnC()
Dim FinalRow As Integer
FinalRow = Cells(65536, 1).End(xlUp).Row

If WorksheetFunction.CountBlank(Range("C2:C" & FinalRow)) > 0 Then
MsgBox "The following cells in column C are empty, please rectify: " & vbCrLf & vbCrLf & _
Range("C:C").SpecialCells(xlCellTypeBlanks).Address, vbCritical, "--- ERROR! ---"
End If
End Sub


How can I make the message box not display the $ for the cell address?.
So for example $C$2 displays as C2.

Thanks,

Marcster.

[Zack Barresse]

.Address(0, 0)

or

.Address(False, False)

[malik641]

Here's something a little cleaner :thumb

Option Explicit

Sub CheckColumnC()
Dim c As Range
Dim msg As String
Dim start As String
Dim finish As String

For Each c In Range("C2:C500")
If c = "" Then
If start = "" Then
start = c.Address(0, 0)
Else
finish = c.Address(0, 0)
End If
Else
If start <> "" And finish <> "" Then
msg = msg & start & ":" & finish & vbCrLf
start = ""
finish = ""
ElseIf start <> "" And finish = "" Then
msg = msg & start & vbCrLf
start = ""
End If
End If
Next c

If start <> "" And finish <> "" Then msg = msg & start & ":" & finish

MsgBox "The following cells are empty:" & vbCrLf & vbCrLf & msg
End Sub

[Marcster]

If I change the line:


Range("C:C").SpecialCells(xlCellTypeBlanks).Address, vbCritical, "--- ERROR! ---"
To:
Range("C:C").SpecialCells(xlCellTypeBlanks).Address(ReferenceStyle:=xlR1C1), vbCritical, "--- ERROR! ---"

This displays the cell address in RC notation.
But if possible I would like the address to be displayed without the $.
Marcster.

[Marcster]

Just seen your posts Firefytr and Malik641.

Cool. Thank you :thumb Just going to check it.

Marcster.

[Norie]

What code are you using?

Did you see firefytr's post?

[Marcster]

Thanks.

I'm going to use this code for now:

Sub CheckColumnC()
Dim FinalRow As Integer
FinalRow = Cells(65536, 1).End(xlUp).Row
If WorksheetFunction.CountBlank(Range("C2:C" & FinalRow)) > 0 Then
MsgBox "The following cells in column C are empty, please rectify: " & vbCrLf & vbCrLf & _
Range("C:C").SpecialCells(xlCellTypeBlanks).Address(0, 0), vbCritical, "--- ERROR! ---"
End If
End Sub

:beerchug:
Marcster.