PDA

View Full Version : Check for Blank Cells in Column



BexleyManor
06-06-2005, 03:36 AM
Hi All,

A nice easy one for you on this Monday morning!!

I'm looking for a worksheet formula that starting at a specified cell, say C3, it will look down the column and identify any cell that is blank then tally these blank cells up in say cell C99 which in this instance is the bottom of my column of data.

I've been shown this in the past but can't for the life of me remember how I did it. :doh:

Any suggestions my super coding friends ?? :bow:

Many thanks

Richie(UK)
06-06-2005, 04:01 AM
Hi BM,

How about :

=COUNTIF(C3:C98,"")

BexleyManor
06-06-2005, 04:06 AM
Richie, I love you in a totally manly platonic way!! You are the man!!

Incidentally, do you know of a good web resource for worksheet formula so I don't have to bother you guys with stupidly simple formula questions ??

Many thanks

Richie(UK)
06-06-2005, 04:25 AM
Hi BM,

You're welcome :)

You can ask non-vba questions at MrExcel (http://www.mrexcel.com/board2/index.php?sid=629896aa0156444c70b03ad51cf3ba1d) and at OzGrid (http://www.ozgrid.com/forum/). However, I'm sure there won't be any complaints here if you post the odd non-vba query - there are plenty of people who are knowledgeable in both areas.

Zack Barresse
06-06-2005, 11:53 AM
Hi BM,

How about :

=COUNTIF(C3:C98,"")

How about ..

=COUNTBLANK(C3:C98)

:devil:

Richie(UK)
06-06-2005, 12:22 PM
How about ..

=COUNTBLANK(C3:C98)

:devil:See what happens when a VBA guy tries to help with a formula! :biglaugh:

MWE
06-06-2005, 01:51 PM
See what happens when a VBA guy tries to help with a formula! :biglaugh:

what this really shows is that there are MANY ways to solve most problems. One of the more valuable aspects of this forum is that one sees lots of ways to solve problems: using vba code, standard excel methods and combinations of the two:thumb.

lucas
06-06-2005, 07:10 PM
BexleyManor,
You might take a look at this. Its called "The Excel Function Dictionary"

http://www.rondebruin.nl/files/xlfdic01.zip
Its a zipped excel file.

BexleyManor
07-05-2005, 03:02 AM
Wow, thanks for all of this folks, really appreciated.

Best damn forum in town. No digity, no doubt!!

Bob Phillips
07-05-2005, 04:41 AM
Wow, thanks for all of this folks, really appreciated.

Best damn forum in town. No digity, no doubt!!

Without trying to b e provocative, I think you will find that the best forum for worksheet formulae is the Microsoft.Public.Exce.Worksheet.functions newsgroup. Many web portals, such as Microsoft and ExcelForum, link to this but IMO direct access to the NG via a newsreader is the best way.

You will find some of the best worksheet formulae guys around posting there, Peo Sjoblom, Aladin Akyurek, Domenic, JE McGimpsey, Bernie Dietrick, and the great Harlan Grove, et al. These guys just cannot be bettered.

Even Anne visit quite regulalrly at the moment :)

DaveBSC
03-12-2007, 10:16 AM
How about ..

=COUNTBLANK(C3:C98)

:devil:

I would like to use the COUNTBLANK function within a FOR loop so that I can check about 100 columns for blank data, one column at a time, and perform additional operations on any column that contains blanks. I have tried converting a column-counter value to a string value (col 4 becomes col D) and then assembling a string using the col string, and passing the string into Countblank in the following way: Application.Countblank(myString), but this returns the value 1. My hope is that using COUNTBLANK will be faster than the nexted FOR loop that I have that uses ISBLANK to check each cell. Can anyone help me with this?

Bob Phillips
03-12-2007, 11:08 AM
For i = 1 To 100
LastRow = Cells(Rows.Count,i).End(xlUp).Row
If Application.CountBlank(Cells(1,i).Resize(LastRow)) > 0 Then
'... your code
End If
Next i

asingh
03-12-2007, 06:22 PM
What is the URL for:



I think you will find that the best forum for worksheet formulae is the Microsoft.Public.Exce.Worksheet.functions newsgroup. Many web portals, such as Microsoft and ExcelForum, link to this but IMO direct access to the NG via a newsreader is the best way.



regards,
asingh

Bob Phillips
03-13-2007, 12:36 AM
It is not a URL. You have to point your newsreader client such as Outlook Express, at the MS news server, msnews.microsoft.com, and subscribe to the
Excel groups.