How do I determine if a row is blank?

[TotallyConfused]

Hello all you MrExcel experts

I have a row of cells ranging from 'A1:E1'. All of these cells may be blank or one of them may contain some alphanumeric data. This data will be put there by VBA, so there are no formulas involved. If one of the cells is NOT blank, I need to know, however, I don't care which column that cell is in. To put it simply, 'Is that row completely blank, YES, or NO?'

I know I could set up a loop to go through the five cells one at a time, and then use an 'IF' command to test if it was blank. I hate to do that because in the worksheet there will be more than the five cells, and also there will be many rows to test. I'm hoping there is something in VBA that will allow me to test that row as a group (or range) to determine if it is blank or not.

I've tried different variations of the following code, and it works, however, it will always give the same answer, regardless of whether all cells are empty or not.

[COLOR=black][FONT=Calibri]Sub Test4Blanks()[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]    Dim Check As Range[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("Test")[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]    Set Check = ws1.Range("A1:E1").Find(What:="", LookAt:=xlWhole, SearchOrder:=xlByColumn)[/FONT][/COLOR]
  
  [COLOR=black][FONT=Calibri]    If Check Is Nothing Then[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]        MsgBox "All blank"[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]    Else[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]         MsgBox "Not blank"[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]    End If[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]End Sub[/FONT][/COLOR]

I'd appreciate any suggestions you may have. Thank you in advance for your help.

TotallyConfused

 

[JLGWhiz]

If Application.CountA(Rows(1)) = 0 Then
    MsgBox "Row is Blank"
End If

One approach.

 

[TotallyConfused]

If Application.CountA(Rows(1)) = 0 Then
    MsgBox "Row is Blank"
End If

One approach.

Hello JLGWhiz

THANK YOU for your prompt answer. It works like a charm. I'm sure the end user's computer will thank you too, because your solution saved it several thousand loops.

TotallyConfused (well maybe slightly less confused now )

 

[TotallyConfused]

Hello JLGWhiz

I have a supplementary question as a followup to my posting #1 . Using the same data sample, I have loaded rows 'A1:E2' into an array called 'ARY'.
This sample array will have two rows with five elements (columns) in each row. I need to check each row of this array to determine if that row is empty (blank). As I stated in my posting #1, it doesn't matter where some data is at in the array row, I just need to know if that row is empty or not.

[COLOR=black][FONT=Arial]
Sub MyAry()[/FONT][/COLOR]
   [COLOR=black][FONT=Arial]Dim Ary() As Variant    [/FONT][/COLOR]
   [COLOR=black][FONT=Arial]Dim NumCol As Long[/FONT][/COLOR]
   [COLOR=black][FONT=Arial]Dim NumRows As Long[/FONT][/COLOR]

   [COLOR=black][FONT=Arial]NumCol = 5             ' It is possible this could vary, so I'm using a variable[/FONT][/COLOR]
   [COLOR=black][FONT=Arial]NumRows = 2         ' This will vary[/FONT][/COLOR]
   [COLOR=black][FONT=Arial]Ary = Range("A1:E" & NumRows[/FONT][/COLOR]
[COLOR=black][FONT=Arial]End Sub[/FONT][/COLOR]

I can loop through the array rows with a FOR 1 To NumRows loop, though I wish I didn't have to because of the large number of rows that exist.
After reading the help file for CountA (and the other Count commands) I'm not sure if that will even work on an array like mine. I tried to modify your solution from posting #2, but I can't figure out how to include all five columns for each row of the array.

Is there any way, or any other VBA command, that can be used to check all the array columns for each row, without using a second FOR loop?

Thank you for any help or suggestions you may be able to offer.

TotallyConfused

 

[JLGWhiz]

Hello JLGWhiz

THANK YOU for your prompt answer. It works like a charm. I'm sure the end user's computer will thank you too, because your solution saved it several thousand loops.

TotallyConfused (well maybe slightly less confused now )

You are welcome,
regards, JLG

 

[sijpie]

TotallyConfused, once you have your data in an array, then looping is easy and very, very quick even for very large arrays. The code below runs in less than a second for over a million cells

Sub t()
    Dim Ary As Variant
    Dim UB1 As Long, UB2 As Long, lR As Long, lC As Long
    Dim sOutp As String
    
    Ary = Range("A1", "aE50000")
    
    'get array size (rows, columns)
    UB1 = UBound(Ary, 1)
    UB2 = UBound(Ary, 2)
    
    sOutp = "The following rows are not empty: "
    
    For lR = 1 To UB1
        For lC = 1 To UB2
            If Not IsEmpty(Ary(lR, lC)) Then
                sOutp = sOutp & lR & ", "
                Exit For    'no need to check the remaining cells in this row
            End If
        Next lC
    Next lR
    
    MsgBox sOutp
End Sub