VBA MSGBOX if column is blank

[jfin1ty]

Hi all,

Hoping someone can help me with a problem I'm having with a sheet I have been working on.
I think it must be a super simple fix but i just can't seem to get it to work.

Basically I have the below report that generates as the result of some lengthy code.
Right before the macro finishes running, I need a VBA MSGBOX to appear and say "There are cells that are not blank in this range" if any cell in column R (other than R2) is not blank.
Everything I have tried so far to make this happen results in the msgbox always appearing regardless if the cells are empty or not.
I'm thinking that the cells in column R are showing as empty but may have some formatting applied to them that im not seeing so they aren't actually "blank".
It could also have to do with the fact the last row is way further down the sheet than it needs to be. Is there a way to reset the last used row to be the Grand total row?
If it helps, if any cells in column R are ever actually not blank, they will always be values and those values will always be greater than 0.

Book56.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	From	Product	Priority	Tonnes (t)	Customer	Destination	.	.	.	.	.	Coils	RC	RC (PAL)	Purl	Slits	Mini T/H's	No Pack Type	Zone
2															3				ZONE 2
3												6							ZONE 2
4																	4		ZONE 2
5													2						ZONE 2
6													13						ZONE 2
7
8															11	1	4		ZONE 3
9
10													1			1	1		ZONE 4
11
12																2			ZONE 5
13															4				ZONE 5
14													1		7	9	33		ZONE 5
15
16													6						ZONE 6
17																6	2		ZONE 6
18													2		3				ZONE 6
19																	1		ZONE 6
20													1						ZONE 6
21
22																1	11		ZONE 2
23													1						ZONE 2
24																	3		ZONE 2
25																	6		ZONE 2
26													1				1		ZONE 3
27															4				ZONE 5
28																18			ZONE 5
29													1						ZONE 5
30													2						ZONE 6
31	WP	CIPA		257.948	Grand	Total						6	31		32	47	66		182	Total
Daily Plan Final

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:T223	Expression	=SEARCH("Total",$F1)>0	text	NO

 

[kevin9999]

Try the following on your data. If the message box appears then you have something in that range, a space, a formula returning a blank, a non ASCII character - something other than conditional formatting (which shouldn't be causing the problem). The code assumes that the grand total row will always have something in column A.

Sub Empty_R()
    Dim ws As Worksheet
    Set ws = Worksheets("Daily Plan Final")
    If WorksheetFunction.CountA(ws.Range("R3:R" & ws.Cells(Rows.Count, "A").End(xlUp).Row - 1)) > 0 _
    Then MsgBox "There are cells that are not blank in this range"
End Sub

 

[jfin1ty]

Try the following on your data. If the message box appears then you have something in that range, a space, a formula returning a blank, a non ASCII character - something other than conditional formatting (which shouldn't be causing the problem). The code assumes that the grand total row will always have something in column A.

Sub Empty_R()
    Dim ws As Worksheet
    Set ws = Worksheets("Daily Plan Final")
    If WorksheetFunction.CountA(ws.Range("R3:R" & ws.Cells(Rows.Count, "A").End(xlUp).Row - 1)) > 0 _
    Then MsgBox "There are cells that are not blank in this range"
End Sub

Hi Kevin,
Interestingly enough, your code works for me.
When I run it and deliberately have values in column R, it tells me there are values there with the MSGbox.
When I run it and deliberately leave all values out of column R, I get no MSGbox.
Maybe it was the syntax of the code I was using beforehand to try and get the result I wanted but in any case I am able to use your code to achieve what i need.
Thanks.

 

[kevin9999]

Hi Kevin,
Interestingly enough, your code works for me.
When I run it and deliberately have values in column R, it tells me there are values there with the MSGbox.
When I run it and deliberately leave all values out of column R, I get no MSGbox.
Maybe it was the syntax of the code I was using beforehand to try and get the result I wanted but in any case I am able to use your code to achieve what i need.
Thanks.

Happy to help & thanks for the feedback As you say, it was probably a syntax thing (easily done).