• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Empty cell in range

Tom22

Member
Hi,

I need a vba code where code will check one column...like column A and find out empty cell in Column A and pop out message.

Please note range in column A will vary from month to month.....hence we need code which analysis col A according to its range and then find out empty cell .

If possible, can tell which cell is empty...like msg says "Cell A584 and A 1067 are empty"

File attached for reference.

Many Thanks
 

Attachments

vletm

Excel Ninja
Tom22
Something like this?
Press [ List ]-button
... but this checks cells only from UsedRange ... not from all possible cells from A-column.
 

Attachments

Marc L

Excel Ninja
Hi !​
As a starter :​
Code:
Sub Demo1()
    Const F = "TRANSPOSE(IF(ISBLANK(#),ADDRESS(ROW(#),1,4)))"
    V = Filter(Evaluate(Replace(F, "#", ActiveSheet.UsedRange.Columns(1).Address)), False, False)
    If UBound(V) > -1 Then MsgBox Join(V, " & "), vbInformation, " Empty cells :"
End Sub
Do you like it ? So thanks to click on bottom right Like !
 

Marc L

Excel Ninja
For those who are not formula friendly an easy way :​
Code:
Sub Demo2()
    With ActiveSheet.UsedRange.Columns(1)
        If Application.CountBlank(.Cells) Then MsgBox .SpecialCells(xlCellTypeBlanks).Address(0, 0), vbInformation, " Empty cells :"
    End With
End Sub
You may Like it !​
 

Tom22

Member
Hi ..

Thanks for looking into this.

@veltm ...your soulution is working fine on the attached sheet but issue is ...when i try to use same on my original file ,this code giving me empty cell which are within the range and also all empty cells whcih are out of range..so i have around 50 odds cells shown as empty where as i have only 2 cells.Any specific reason for the same?

@ Marc.....your Demo 1 doesnot throw any mesage of empty cells, where as Demo2 thros Debug message “Run time error 1004”...Attaching update file with your codes

Please suggest something on this
 

Attachments

vletm

Excel Ninja
Tom22
Your sample file should be as near as possible with Your original file.
Your Test.xlsm-file seems to have same data than Book2.xlsm-file.

You haven't given 'range' in which You would like to check those 'empties'.
where code will check one column...like column A
What is the maximum number of rows in Excel?
maximum number of spreadsheet rows supported by Excel 97, Excel2000, Excel 2002 and Excel 2003.
(Excel 2007, 2010 and 2013 support 1,048,576 rows ).

... my code checks cells only from UsedRange
 

Tom22

Member
Yes vletm....test file is same as book1..as I told earlier range will differ every month so code should be flexible to find empty cell in range.
it should check it in col A only...iam using 2016 version so have 1,048,576 rows
 

vletm

Excel Ninja
It depends of my (Your) original file, how many empty cells there would be.
As I've written UsedRange .. is range which You have used
Number of row depends ... eg how have You cleared old data.
Yours where code will check one column...like column A ...
Means also that ... there are 1047228 empty cells in column A in Your sample file.
But ... I'm sure that You don't want to see that kind of list!

> Check this version ... it could give some new data
 

Attachments

Marc L

Excel Ninja
your Demo 1 doesnot throw any mesage of empty cells, where as Demo2 thros Debug message “Run time error 1004”...
As my both demonstrations well work on my side with your initial attachment under different Excel versions …​
From your new attachment, Demo1 not founds any empty cell as you can check yourself​
- and compare with the initial attachment easily -​
for example from cell A1 apply the key combo Ctrl(down arrow) the new cell selected must be A583 if A584 is empty (*)
which is the case with your initial attachment but not on your last one !​
The same when checking via this formula in cell B1 for example : =ISBLANK(A584)
which returns TRUE on initial attachment but FALSE on last …​
Demo2 issue comes from using SpecialCells when nothing matches (so could be a mess).​
But this should be avoided with the use of the COUNTBLANK worksheet function but not as it returns 2 …​
So the question is what happens between your both attachments ?‼​
With such worksheet the ultimate way is to use the Range.Find method (to see in VBA inner help) avoiding a For … Next loop …​
But as a formula is smart enough to find cells without value rather than empty cells (*) this is my Demo1 revamped :​
Code:
Sub Demo1r()
    Const F = "TRANSPOSE(IF(#="""",ADDRESS(ROW(#),1,4)))"
    V = Filter(Evaluate(Replace(F, "#", ActiveSheet.UsedRange.Columns(1).Address)), False, False)
    If UBound(V) > -1 Then MsgBox Join(V, " & "), vbInformation, " Cells without value :"
End Sub
You should Like it !​
(*) Empty cell definition seems to be different between Excel and VBA as you saw in vletm's code …
 

Tom22

Member
It depends of my (Your) original file, how many empty cells there would be.
As I've written UsedRange .. is range which You have used
Number of row depends ... eg how have You cleared old data.
Yours where code will check one column...like column A ...
Means also that ... there are 1047228 empty cells in column A in Your sample file.
But ... I'm sure that You don't want to see that kind of list!

> Check this version ... it could give some new data
@ Vletm....I will be deleting old data and then replacing it with new data in the file every month.

Then I will run this code to check empty cell in col.A with in the range.
Not sure if I got you correctly here
 

Tom22

Member
Ye
As my both demonstrations well work on my side with your initial attachment under different Excel versions …​
From your new attachment, Demo1 not founds any empty cell as you can check yourself​
- and compare with the initial attachment easily -​
for example from cell A1 apply the key combo Ctrl(down arrow) the new cell selected must be A583 if A584 is empty (*)
which is the case with your initial attachment but not on your last one !​
The same when checking via this formula in cell B1 for example : =ISBLANK(A584)
which returns TRUE on initial attachment but FALSE on last …​
Demo2 issue comes from using SpecialCells when nothing matches (so could be a mess).​
But this should be avoided with the use of the COUNTBLANK worksheet function but not as it returns 2 …​
So the question is what happens between your both attachments ?‼​
With such worksheet the ultimate way is to use the Range.Find method (to see in VBA inner help) avoiding a For … Next loop …​
But as a formula is smart enough to find cells without value rather than empty cells (*) this is my Demo1 revamped :​
Code:
Sub Demo1r()
    Const F = "TRANSPOSE(IF(#="""",ADDRESS(ROW(#),1,4)))"
    V = Filter(Evaluate(Replace(F, "#", ActiveSheet.UsedRange.Columns(1).Address)), False, False)
    If UBound(V) > -1 Then MsgBox Join(V, " & "), vbInformation, " Cells without value :"
End Sub
You should Like it !​
(*) Empty cell definition seems to be different between Excel and VBA as you saw in vletm's code …
Yes Marc...my latest attachment is not different from 1st one except it has something in excel that I doesn't take us to the cell 584 if we press control and down key....so that;s why guess demo1 code not giving us any message..

your revised code for Demo1r() is working perfectly on this but can you please explain what I need to change in the code if my col is not A but col U.....I can think of (15) instead of (1) here"ActiveSheet.UsedRange.Columns(1)." but what about Const F = "TRANSPOSE(IF(#="""",ADDRESS(ROW(#),1,4)))"?????
 

vletm

Excel Ninja
Tom22
I depend HOW do You 'delete' You data...
Many times 'empty' cells are not 'empty' ... those could only look like 'empty'.
So far, You cannot set manually any range!
You should upload here Your 'challenge data'.
 

Marc L

Excel Ninja
I will be deleting old data and then replacing it with new data in the file every month.
To have a 'clean' worksheet in order to well works with the UsedRange method, delete any 'empty' row remaining.​
For an unclean worksheet as the Range.End method may fail, again the ultimate way to find the last data cell is Range.Find …​
I can think of (15) instead of (1)
Yes 15 if column O ‼​
Or you can use the column letter between double quotes …​
but what about Const F
The constant F is for the worksheet Formula to apply and must stay as it is, no mod is necessary (see post #21) …​
 

Tom22

Member
Vletm...yes it seems so...cell 584 and cell 1067 are looking empty but they are not....but I have not changed anything and I will be getting this kind of data every month..so test file which I shared is my challenge data.....how can we overcome these kind of scenarios
 

Tom22

Member
To have a 'clean' worksheet in order to well works with the UsedRange method, delete any 'empty' row remaining.​
For an unclean worksheet as the Range.End method may fail, again the ultimate way to find the last data cell is Range.Find …​
Sorry Marc...but didn't get you here.....can you modified your code if my data is col U instead of col A....
 

vletm

Excel Ninja
Tom22
You wrote with #5 reply
when i try to use same on my original file ,this code giving me empty cell which are within the range and also all empty cells whcih are out of range
Is there two files or not?
Which file gives unwanted results?
 

Tom22

Member
Read again post #13 as I just finished to edit it …​
okay I tried to do it again by filling my data in col. U which is number 21 and changed Column (21) but still it is giving me empty cell for col A and there are too many........attaching another file with modified code...can you please look into it...what is missing here
 

Attachments

Tom22

Member
Tom22
You wrote with #5 reply
when i try to use same on my original file ,this code giving me empty cell which are within the range and also all empty cells whcih are out of range
Is there two files or not?
Which file gives unwanted results?
Vletm...there are 2 files but with same data.....and Test file is giving me unwanted results
 

vletm

Excel Ninja
Tom22
my (Your) original - Book2.xlsm - Test.xlsm -- Test_v1.xlsm
If You copy & paste data from file to other ... pasted file's data could be different.
Have You tried to use FILTER to ... filter 'blank' cells?
 

Marc L

Excel Ninja
changed Column (21) but still it is giving me empty cell for col A and there are too many
My bad and your bad !​
  • My bad : constant formula must be changed, 1 must becomes 21 : Const F = "TRANSPOSE(IF(#="""",ADDRESS(ROW(#),21,4)))"
    Just reading the Excel help of ADDRESS worksheet function, at child level …

    Or as you always change the source worksheet : Const F = "TRANSPOSE(IF(#="""",ADDRESS(ROW(#),COLUMN(#),4)))"

  • Your bad : why are you always changing the source worksheet layout ? A non sense ‼
    Is your real file with only an unique column filled ?! In this case it's the first column of UsedRange.
    Or better use the Range.Find method to find out the last filled cell and the column header as well …
As a reminder : bad attachment, bad code !​
In this case that means you are very confident with your Excel / VBA skills in order to amend any shared code …​
 

Tom22

Member
My bad and your bad !​

  • My bad : constant formula must be changed, 1 must becomes 21 : Const F = "TRANSPOSE(IF(#="""",ADDRESS(ROW(#),21,4)))"
    Just reading the Excel help of ADDRESS worksheet function, at child level …

    Or as you always change the source worksheet : Const F = "TRANSPOSE(IF(#="""",ADDRESS(ROW(#),COLUMN(#),4)))"

  • Your bad : why are you always changing the source worksheet layout ? A non sense ‼
    Is your real file with only an unique column filled ?! In this case it's the first column of UsedRange.
    Or better use the Range.Find method to find out the last filled cell and the column header as well …
As a reminder : bad attachment, bad code !​
In this case that means you are very confident with your Excel / VBA skills in order to amend any shared code …​
Marc.... I have changed it to 21 in Transpose line and then again in columns (21)..but it giving me lots of cell which are out of range
 

Marc L

Excel Ninja
#21 ... still no work
V keeps empty
I have changed it to 21 in Transpose line and then again in columns (21)..but it giving me lots of cell which are out of range
As I receive any answer to my question … But for both as I yet explained :​
In this case it's the first column of UsedRange.
As no issue on my side, according to the last last attachment as there is only a single column in the used range ‼ :rolleyes:
Column U is the first column of the used range, not the #21 that's it !​
Using Range.Find method to find out the column header and the column last filled cell is the ultimate way …​
Future not belongs only to those waking up early but for those who just well read …
 

Marc L

Excel Ninja
According to post #18 attachment as I yet wrote all the necessary in post #21 (child level reading) :​
Or as you always change the source worksheet : Const F = "TRANSPOSE(IF(#="""",ADDRESS(ROW(#),COLUMN(#),4)))"
Is your real file with only an unique column filled ?! In this case it's the first column of UsedRange.
Code:
Sub Demo1R2D2()
    Const F = "TRANSPOSE(IF(#="""",ADDRESS(ROW(#),COLUMN(#),4)))"
    V = Filter(Evaluate(Replace(F, "#", ActiveSheet.UsedRange.Columns(1).Address)), False, False)
    If UBound(V) > -1 Then MsgBox Join(V, " & "), vbInformation, " Cells without value :"
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
Top