CODEWITHSUNDEEP
vbaexcel
user4974730
user4974730

Reputation:

What cells are empty in given range

I have a given range which I get for empty numbers. I want to tell user which cells are empty. This is function:

Sub check()
'   Goal: check if there are any entry truth table cells for outputs
Dim outputsrange As range
Set outputsrange = range(inputnum + 1 & ":" & inputnum + outputnum)
If IsEmpty(outputsrange) Then
    MsgBox ("The following cells are empty:" & vbNewLine & emptycell)
Else
    Call makekmap
End If
End Sub

What do I put where "emptycell" is?

EDIT: new code, still doesn't run but has correct object definitions.

Sub check()
Dim outputsrange As range, emptycells As range
Set outputsrange = range(inputnum + 1 & ":" & cases)
Set emptycells = outputsrange.SpecialCells(xlCellTypeBlanks)
If Not emptycells Is Nothing Then
    MsgBox "The following cells are empty:" & vbNewLine & emptycells.Address
Else
    MsgBox ("No cells are empty")
End If
End Sub

Upvotes: 2

Views: 140

Answers (2)

Dirk Reichel
Dirk Reichel

Reputation: 7979

A different way to avoid errors:

Sub check()
  Dim outputsrange As Range, runner As Variant
  Set outputsrange = Range(inputnum + 1 & ":" & inputnum + outputnum).CurrentRegion
  For Each runner In outputsrange.Formula
    If Len(runner) = 0 Then
      MsgBox "The following cells are empty:" & vbNewLine & _
      outputsrange.SpecialCells(xlCellTypeBlanks).Address(0, 0)
      Exit Sub
    End If
  Next
  Call makekmap
End Sub

EDIT

Taking the answer from Jeeped and the comment from Tim Williams you could merge it with my solution to:

Sub check()
  Dim outputsrange As Range, runner As Variant
  Set outputsrange = Range(inputnum + 1 & ":" & inputnum + outputnum).CurrentRegion
  If Application.CountBlank(outputsrange) Then
    MsgBox "The following cells are empty:" & vbNewLine & _
      outputsrange.SpecialCells(xlCellTypeBlanks).Address(0, 0)
    Exit Sub
  End If
  Call makekmap
End Sub

Should be the fastest way to get what you want and also not pop any errors as log as the outputsrange itself is valid.

Upvotes: 2

user4039065
user4039065

Reputation:

Check if the Range.SpecialCells method with the xlCellTypeBlanks option reports blank cells and output the unioned Range.Address property if they exist.

Dim inputnum As Long, outputnum As Long
Dim outputsrange As Range, mtCells As Range
inputnum = 1: outputnum = 6
Set outputsrange = Range(inputnum + 1 & ":" & inputnum + outputnum)
On Error Resume Next
Set mtCells = outputsrange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not mtCells Is Nothing Then
    MsgBox "The following cells are empty:" & vbNewLine & _
           mtCells.Address(0, 0)
Else
    Call makekmap
End If

Please note that cells containing formulas that return zero-length strings (e.g. "" are not truly blank.

Alternate without On Error Resume Next

Dim inputnum As Long, outputnum As Long
Dim outputsrange As Range
inputnum = 1: outputnum = 6
Set outputsrange = Range(inputnum + 1 & ":" & inputnum + outputnum)
If CBool(Application.CountBlank(outputsrange)) Then
    MsgBox "The following cells are empty:" & vbNewLine & _
           outputsrange.SpecialCells(xlCellTypeBlanks).Address(0, 0)
Else
    Call makekmap
End If

You will have to be careful with that because the worksheet's COUNTBLANK function will count cells containing zero-length strings as blanks but the SpecialCells(xlCellTypeBlanks) will not. Potentially, you could have a situation where you enter the MsgBox area but have nothing to report.

Upvotes: 2

Related Questions