How to check if a range of cells is empty using VBA?

[Mr2017]

Hi

Can someone please advise what the quickest way of checking if a range of cells eg A1:Y1 is empty, using VBA, please?

I found this code online, but I'm not sure how to modify it....?

<code class="keyword" style="box-sizing: border-box; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; font-weight: bold !important; direction: ltr !important; display: inline !important; color: rgb(0, 102, 153) !important;">If</code> <code class="plain" style="box-sizing: border-box; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; direction: ltr !important; display: inline !important;">IsEmpty(Cell) </code><code class="keyword" style="box-sizing: border-box; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; font-weight: bold !important; direction: ltr !important; display: inline !important; color: rgb(0, 102, 153) !important;">Then</code>

<tbody style="box-sizing: border-box; border: 0px !important; margin: 0px !important; padding: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; font-size: 1em !important; direction: ltr !important;">
</tbody>

<code style="box-sizing: border-box; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; margin: 0px !important; padding: 0px 0.3em 0px 0px !important; border: 0px !important; outline: 0px !important; background: none !important; text-align: right !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: 2.7em !important; line-height: 1.1em !important; direction: ltr !important; display: block !important;">2</code>

<code class="spaces" style="box-sizing: border-box; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; direction: ltr !important; display: inline !important;"> </code><code class="plain" style="box-sizing: border-box; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; direction: ltr !important; display: inline !important;">StatementsIfCellIsEmpty</code>

<tbody style="box-sizing: border-box; border: 0px !important; margin: 0px !important; padding: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; font-size: 1em !important; direction: ltr !important;">
</tbody>

<code style="box-sizing: border-box; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; margin: 0px !important; padding: 0px 0.3em 0px 0px !important; border: 0px !important; outline: 0px !important; background: none !important; text-align: right !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: 2.7em !important; line-height: 1.1em !important; direction: ltr !important; display: block !important;">3</code>

<code class="keyword" style="box-sizing: border-box; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; font-weight: bold !important; direction: ltr !important; display: inline !important; color: rgb(0, 102, 153) !important;">Else</code>

<tbody style="box-sizing: border-box; border: 0px !important; margin: 0px !important; padding: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; font-size: 1em !important; direction: ltr !important;">
</tbody>

<code style="box-sizing: border-box; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; margin: 0px !important; padding: 0px 0.3em 0px 0px !important; border: 0px !important; outline: 0px !important; background: none !important; text-align: right !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: 2.7em !important; line-height: 1.1em !important; direction: ltr !important; display: block !important;">4</code>

<code class="spaces" style="box-sizing: border-box; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; direction: ltr !important; display: inline !important;"> </code><code class="plain" style="box-sizing: border-box; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; direction: ltr !important; display: inline !important;">StatementsIfCellIsNotEmpty</code>

<tbody style="box-sizing: border-box; border: 0px !important; margin: 0px !important; padding: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; font-size: 1em !important; direction: ltr !important;">
</tbody>

<code style="box-sizing: border-box; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; margin: 0px !important; padding: 0px 0.3em 0px 0px !important; border: 0px !important; outline: 0px !important; background: none !important; text-align: right !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: 2.7em !important; line-height: 1.1em !important; direction: ltr !important; display: block !important;">5</code>

<code class="keyword" style="box-sizing: border-box; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; font-weight: bold !important; direction: ltr !important; display: inline !important; color: rgb(0, 102, 153) !important;">End</code> <code class="keyword" style="box-sizing: border-box; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; margin: 0px !important; padding: 0px !important; border: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; font-weight: bold !important; direction: ltr !important; display: inline !important; color: rgb(0, 102, 153) !important;">If</code>

<tbody style="box-sizing: border-box; border: 0px !important; margin: 0px !important; padding: 0px !important; outline: 0px !important; background: none !important; float: none !important; vertical-align: baseline !important; position: static !important; left: auto !important; top: auto !important; right: auto !important; bottom: auto !important; height: auto !important; width: auto !important; line-height: 1.1em !important; font-size: 1em !important; direction: ltr !important;">
</tbody>

TIA

 

[JLGWhiz]

Formula

=If( CountA(A2:F2) = 0, "Empty", "Not Empty")

VBA

If.Application.CountA(Range("A2:F2") = 0 Then
    MsgBox "Empty"
Else
    MsgBox "Not Empty"
End If

the IsEmpty function is not intended to check if a cell contains data. It was designed to test variables to see if they were initialized.

 

[Mr2017]

Hi JLGWhiz

Apologies for the delay in responding.

When I copy and paste the code above, as a separate Sub (to test it), I get a "Compile error" that says "Expected: Identifier..."

Do you know what is missing?

Sub If()


If.Application.CountA(Range("A2:F2") = 0 Then


MsgBox "Empty"
Else
MsgBox "Not Empty"
End If


End Sub


Thanks in advance.

 

[Mr2017]

Actually, I found a solution online (below), so it's ok. Thank you for the prompt response, however.

Sub Empty()


If WorksheetFunction.CountA(Range("A5:ac5")) = 0 Then
MsgBox "Empty"
Else
MsgBox "Not Empty"
End If
End Sub