What am I doing wrong with this Excel vba-CodePudding

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 means
```
Dim cell As Range: Set cell = Nothing
```
Similarly, when you do Dim fIsEmpty As Boolean, it actually means
```
Dim 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 is False i.e. doing fIsEmpty = 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 using rg.CountLarge
or rg.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