I'm attempting to check and see if each column is empty or not, but it's not working. What am I doing wrong? I need it for more calculations. If I could get some help that would be great. I need to do it for nine of the columns f through n and for each I need that value as a Boolean afterwards which is why I made it the way I did I made it based on a tutorial. I'm trying to get a message that tells me if a column is empty
Sub emptysinder()
Dim cell As Range
Dim fIsEmpty As Boolean
Dim gIsEmpty As Boolean
Dim hIsEmpty As Boolean
Dim iIsEmpty As Boolean
Dim jIsEmpty As Boolean
Dim kIsEmpty As Boolean
Dim lIsEmpty As Boolean
Dim mIsEmpty As Boolean
Dim nIsEmpty As Boolean
fIsEmpty = False
gIsEmpty = False
hIsEmpty = False
iIsEmpty = False
jIsEmpty = False
kIsEmpty = False
lIsEmpty = False
mIsEmpty = False
nIsEmpty = False
For Each cell In Range("F1:F200")
If IsEmpty(cell) = True Then
fIsEmpty = True
Exit For
End If
Next cell
If fIsEmpty = True Then
MsgBox "All cells are empty in your range!"
Else
MsgBox "Cells!"
End If
For Each cell In Range("G1:G200")
If IsEmpty(cell) = True Then
gIsEmpty = True
Exit For
End If
Next cell
If gIsEmpty = True Then
MsgBox "All cells are empty in your range!"
Else
MsgBox "Cells "
End If
For Each cell In Range("H1:H200")
If IsEmpty(cell) = True Then
hIsEmpty = True
Exit For
End If
Next cell
If hIsEmpty = True Then
MsgBox "All cells are empty in your range!"
Else
MsgBox "Cells have values!"
End If
For Each cell In Range("I1:I200")
If IsEmpty(cell) = True Then
iIsEmpty = True
Exit For
End If
Next cell
If iIsEmpty = True Then
MsgBox "All cells are empty in your range!"
Else
MsgBox "Cells have values!"
End If
For Each cell In Range("J1:J200")
If IsEmpty(cell) = True Then
jIsEmpty = True
Exit For
End If
Next cell
If jIsEmpty = True Then
MsgBox "All cells are empty in your range!"
Else
MsgBox "Cells have values!"
End If
For Each cell In Range("K1:K200")
If IsEmpty(cell) = True Then
kIsEmpty = True
Exit For
End If
Next cell
If kIsEmpty = True Then
MsgBox "All cells are empty in your range!"
Else
MsgBox "Cells have values!"
End If
For Each cell In Range("L1:L200")
If IsEmpty(cell) = True Then
lIsEmpty = True
Exit For
End If
Next cell
If lIsEmpty = True Then
MsgBox "All cells are empty in your range!"
Else
MsgBox "Cells have values!"
End If
For Each cell In Range("M1:M200")
If IsEmpty(cell) = True Then
mIsEmpty = True
Exit For
End If
Next cell
If mIsEmpty = True Then
MsgBox "All cells are empty in your range!"
Else
MsgBox "Cells have values!"
End If
For Each cell In Range("N1:N200")
If IsEmpty(cell) = True Then
nIsEmpty = True
Exit For
End If
Next cell
If nIsEmpty = True Then
MsgBox "All cells are empty in your range!"
Else
MsgBox "Cells have values!"
End If
End Sub
CodePudding user response:
Essentially you are checking nine columns for rows 1 through 200 if they are empty or not. Using a loop you can call some custom function (see below) to make the checks.
Option Explicit
Public Sub emptysinder()
Dim i As Long
Dim r As Range
For i = 1 To 9
Set r = Range("F1").Offset(0, i - 1).Resize(200, 1)
If IsAllEmpty(r) Then
Debug.Print "Range " & r.Address & " is all empty."
ElseIf IsAnyEmpty(r) Then
Debug.Print "Range " & r.Address & " is partially empty."
Else
Debug.Print "Range " & r.Address & " filled."
End If
Next i
End Sub
And the sample output on the immediate window
Range $F$1:$F$200 is all empty.
Range $G$1:$G$200 filled.
Range $H$1:$H$200 is all empty.
Range $I$1:$I$200 is partially empty.
Range $J$1:$J$200 is all empty.
Range $K$1:$K$200 is partially empty.
Range $L$1:$L$200 is all empty.
Range $M$1:$M$200 is all empty.
Range $N$1:$N$200 is all empty.
With the following helper functions in a module
Public Function IsAllEmpty(ByVal r_range As Range) As Boolean
Dim Item As Range
For Each Item In r_range,Cells
If Not IsEmpty(Item) Then
IsAllEmpty = False
Exit Function
End If
Next
IsAllEmpty = True
End Function
Public Function IsAnyEmpty(ByVal r_range As Range) As Boolean
Dim Item As Range
For Each Item In r_range.Cells
If IsEmpty(Item) Then
IsAnyEmpty = True
Exit Function
End If
Next
IsAnyEmpty = False
End Function
CodePudding user response:
Check Columns If Empty (or Blank)
Tips
Whenever you need to create a ton of variables holding the same data type, you should consider using a data structure e.g. an array, a collection, a dictionary... etc. Then you can loop through the elements of the data structure and/or easily access individual elements.
When you do
Dim cell As Range
it actually meansDim cell As Range: Set cell = Nothing
Similarly, when you do
Dim fIsEmpty As Boolean
, it actually meansDim fIsEmpty As Boolean: fIsEmpty = False
When declaring a variable, it is always assigned a default value like in both for-mentioned cases. In the first case, it is
Nothing
, while in the second case it isFalse
i.e. doingfIsEmpty = False
is not necessary. If it helps with your understanding of the code, you can keep doing it, there's no harm done except that the code is longer.When looping through the cells of a range, you should consider doing it in this way:
For Each cell In Range("G1:G200").Cells
Note the
.Cells
. By doing this, the code will become more readable and you'll avoid errors occurring when VBA 'might decide' that it is.Rows
or.Columns
(the latter is used in the following codes) or whatnot.Instead of
If IsEmpty(cell) = True Then
you can simply do:If IsEmpty(cell) Then
Again, If it helps with your understanding of the code, you can keep doing it, there's no harm done except that the code is longer.
Empty
You can efficiently check if all the cells in a range are empty by using
Application.CountA
in the following way:If Application.CountA(rg) = 0 Then ' all cells are empty
Sub EmptiesInDer()
' Reference the worksheet ('ws').
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
' Reference the range ('rg').
Dim rg As Range: Set rg = ws.Range("F1:N200")
' Write the number of columns to a variable ('cCount').
Dim cCount As Long: cCount = rg.Columns.Count
' Define a 2D one-based two-row array: the first row will hold
' the addresses, while the second row will hold the booleans.
Dim cArr() As Variant: ReDim cArr(1 To 2, 1 To cCount)
' Declare additional variables.
Dim crg As Range ' Current Column Range
Dim c As Long ' Current Array Column
' Loop through each column of the range.
For Each crg In rg.Columns
c = c 1 ' next array column
' Write the column range address to the first array row.
cArr(1, c) = crg.Address(0, 0)
' Write the boolean (all cells are empty) to the second array row.
cArr(2, c) = IIf(Application.CountA(crg) = 0, True, False)
Next crg
' Access the array values.
' Print the results from the array to the Immediate window (Ctrl G).
Debug.Print "Empty Columns (Common)"
Debug.Print "Column", "Address", "Is Empty"
For c = 1 To cCount
Debug.Print c, cArr(1, c), cArr(2, c)
Next c
' Print the fancy results from the array to the Immediate window (Ctrl G).
Debug.Print "Empty Columns (Fancy)"
For c = 1 To cCount
Debug.Print c & ". The column range '" & cArr(1, c) & "' is " _
& IIf(cArr(2, c), "", "not ") & "empty."
Next c
End Sub
Blank
A cell is considered blank if it is either empty, it contains the formula
=""
, it contains a single quote'
or whatnot (can't think of anything else).You can efficiently check if all the cells in a range are blank by using
Application.CountBlank
in the following ways:If Application.CountBlank(rg) = rg.Rows.Count Then ' all cells in a column range are blank If Application.CountBlank(rg) = rg.Columns.Count Then ' all cells in a row range are blank If Application.CountBlank(rg) = rg.Cells.Count Then ' all cells in a range are blank ' ***
*** There is a limit for
.Cells.Count
so you may consider usingrg.CountLarge
orrg.Rows.Count * rg.Columns.Count
for a range.In this particular case, the first option is used and the value of
rg.Rows.Count
, the number of rows, is written to a variable (rCount
).
Sub BlanksInDer()
' Reference the worksheet ('ws').
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
' Reference the range ('rg').
Dim rg As Range: Set rg = ws.Range("F1:N200")
' Write the number of rows to a variable ('rCount').
Dim rCount As Long: rCount = rg.Rows.Count
' Write the number of columns to a variable ('cCount').
Dim cCount As Long: cCount = rg.Columns.Count
' Define a 2D one-based two-row array: the first row will hold
' the addresses, while the second row will hold the booleans.
Dim cArr() As Variant: ReDim cArr(1 To 2, 1 To cCount)
' Declare additional variables.
Dim crg As Range ' Current Column Range
Dim c As Long ' Current Array Column
' Loop through each column of the range.
For Each crg In rg.Columns
c = c 1 ' next array column
' Write the column range address to the first array row.
cArr(1, c) = crg.Address(0, 0)
' Write the boolean (all cells are blank) to the second array row.
cArr(2, c) = IIf(Application.CountBlank(crg) = rCount, True, False)
Next crg
' Access the array values.
' Print the results from the array to the Immediate window (Ctrl G).
Debug.Print "Blank Columns (Common)"
Debug.Print "Column", "Address", "Is Blank"
For c = 1 To cCount
Debug.Print c, cArr(1, c), cArr(2, c)
Next c
' Print the fancy results from the array to the Immediate window (Ctrl G).
Debug.Print "Blank Columns (Fancy)"
For c = 1 To cCount
Debug.Print c & ". The column range '" & cArr(1, c) & "' is " _
& IIf(cArr(2, c), "", "not ") & "blank."
Next c
End Sub