I prefer using
If Len(Trim(Cells(i, 1).Value))=0 Then Msgbox "Empty"
I prefer using
If Len(Trim(Cells(i, 1).Value))=0 Then Msgbox "Empty"
As suggested by @PatricK you may consider using ISBLANK function instead of IsEmpty function. ISBLANK function Returns TRUE if the value is blank
Using VBA
Sub test()
For i = 1 To 4
MsgBox Evaluate("isblank(" & Cells(i, 1).Address & ")")
Next
End Sub
I have multiple lines in my macro where I am checking to see if the current cell is empty or not. One of the lines works great. Two do not. I have copied the code for the two that don't from the one that does and it still isn't working. I've been stepping through the code line by line and I watch it not execute and I can't figure out where I'm going wrong. I also tried the Googles but no results there seemed to match my problem. Basically, regardless of the value in the active cells, the code always skips straight to "Else", even if one cell is empty and one is not. I have copied this exact code into a separate macro that was just this code and it worked fine. Why on earth would it not work here?
I Trim the cell to get rid of any potential whitespace characters. I'm sure there are more efficient ways to do this but, alas, I am not a code wizard yet. Bool1 and Bool2 are created and initialized to False earlier in the code. Also, the line that works uses the exact same IF statement setup, just with different values. Any help would be greatly appreciated.
Here is the snippet
ActiveCell.Value = Trim(ActiveCell.Value)
If IsEmpty(ActiveCell.Value) = True Then
'cell empty
bool1 = False
Else
'cell has x
bool1 = True
End If
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Trim(ActiveCell.Value)
If IsEmpty(ActiveCell.Value) = True Then
'cell empty
bool2 = False
Else
'cell has x
bool2 = True
End If
well... frankly, it means the cells you're working with aren't empty. i suggest you double-check that you're testing the cells you think you are.
set a breakpoint after the line "ActiveCell.Offset(0, 1).Select", then hit CTRL+G when the program breaks to bring up the Immediate Window. in that window, type "? ActiveCell.Address" and hit enter to see what cell you're actually looking at, then verify that it's empty onscreen. additionally, you can type "? IsEmpty(ActiveCell.Value)" or just "? ActiveCell.Value" and see what pops up.
hope this helps - happy debugging!
What is the point if IsEmpty then? I always used = “” because i never knew about IsEmpty. However, if i had known about it i could see myself trying to use it like OP. What would be a practical use case of the function?
Excel blank cells not empty
VBA IsEmpty Function not working properly
Excel blank cells not empty
IsEmpty function not working in Macro
well... frankly, it means the cells you're working with aren't empty. i suggest you double-check that you're testing the cells you think you are.
set a breakpoint after the line "ActiveCell.Offset(0, 1).Select", then hit CTRL+G when the program breaks to bring up the Immediate Window. in that window, type "? ActiveCell.Address" and hit enter to see what cell you're actually looking at, then verify that it's empty onscreen. additionally, you can type "? IsEmpty(ActiveCell.Value)" or just "? ActiveCell.Value" and see what pops up.
hope this helps - happy debugging!
More on reddit.comwould it be possible for somebody to explain what these cells effectively contain, why they are considered as not empty when they appear as blank and seem to contain nothing [...] and how they could have been generated?
My explanations could be more clear if I could publish the files, but I do not know how to do it.
The cells probably contain the null string ("") as a constant.
One way to create this is to enter the formula ="" into A1, copy A1, then paste-special-value back into A1.
Then CODE(A1) returns #VALUE, COUNTA(A1) returns 1, ISBLANK(A1) returns FALSE, VBA IsEmpty([a1]) returns False, and Asc([a1]) raises a VBA error ("invalid argument")
The cell is not considered empty because in Excel, "empty" means no value (constant or formula).
I'm sure that guess is correct. But for a dispositive answer, yes, it would be good to provide a simple Excel file that demonstrates the problem.
Upload the Excel file to a file-sharing website, and post the download URL in a response here.
I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum. But IMHO, if you use onedrive, be sure the download URL does not allow others to edit the file. That avoids accidental (as well as purposeful) changes. (I'm all thumbs. sigh)
Thanks for taking care
I loaded the test file in my Dropbox account Jacques SIMONNET (******@laposte.fr) in a folder named "Empty" that I shared readonly with Microsoft Community .
Is that OK or do I need to share it with a more precise address?
Best regards.
Something like this?
Sub Check_and_execute
Dim Cell As Range
Dim CellsEmpty as boolean
CellsEmpty = True
For Each Cell In ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
If Cell.Value <> "" Then
CellsEmpty = False
Exit for
End if
Next
If CellsEmpty = True then
'code
Else
MsgBox "Not all cells are empty."
End if
End Sub
No loop needed:
Sub Check_and_execute()
If Application.WorksheetFunction.CountA(ThisWorkbook.Sheets("Sheet1").Range("A1:A10")) > 0 Then
MsgBox "Not all cells are empty."
Exit Sub
End If
'code
End Sub
would it be possible for somebody to explain what these cells effectively contain, why they are considered as not empty when they appear as blank and seem to contain nothing [...] and how they could have been generated?
My explanations could be more clear if I could publish the files, but I do not know how to do it.
The cells probably contain the null string ("") as a constant.
One way to create this is to enter the formula ="" into A1, copy A1, then paste-special-value back into A1.
Then CODE(A1) returns #VALUE, COUNTA(A1) returns 1, ISBLANK(A1) returns FALSE, VBA IsEmpty([a1]) returns False, and Asc([a1]) raises a VBA error ("invalid argument")
The cell is not considered empty because in Excel, "empty" means no value (constant or formula).
I'm sure that guess is correct. But for a dispositive answer, yes, it would be good to provide a simple Excel file that demonstrates the problem.
Upload the Excel file to a file-sharing website, and post the download URL in a response here.
I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum. But IMHO, if you use onedrive, be sure the download URL does not allow others to edit the file. That avoids accidental (as well as purposeful) changes. (I'm all thumbs. sigh)
Thanks for taking care
I loaded the test file in my Dropbox account Jacques SIMONNET (******@laposte.fr) in a folder named "Empty" that I shared readonly with Microsoft Community .
Is that OK or do I need to share it with a more precise address?
Best regards.
I prefer using
If Len(Trim(Cells(i, 1).Value))=0 Then Msgbox "Empty"
As suggested by @PatricK you may consider using ISBLANK function instead of IsEmpty function. ISBLANK function Returns TRUE if the value is blank
Using VBA
Sub test()
For i = 1 To 4
MsgBox Evaluate("isblank(" & Cells(i, 1).Address & ")")
Next
End Sub