TotallyConfused
Board Regular
- Joined
- May 4, 2017
- Messages
- 247
- Office Version
- 365
- Platform
- Windows
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.
I'd appreciate any suggestions you may have. Thank you in advance for your help.
TotallyConfused
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.
Code:
[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]
TotallyConfused