Solved

Excel 2007 Count Blank Cells

Posted on 2014-10-22
11
103 Views
Last Modified: 2014-10-24
I have an Excel 2007 file that is an export (Sheet1) from a Access 2007 database.

I created an additional "main" tab that I want to be able to show just the blank cells. Basically a QC check on the data.

Please see the attached example.
Book1.xlsx
0
Comment
Question by:CMILLER
  • 5
  • 5
11 Comments
 
LVL 30

Expert Comment

by:captain
Comment Utility
Do you mean using conditional formatting to highlight the empty cells?

If so you can use the "Format only cells that contain" option on the cell range and set it to value is equal to ="" and choose a colour that you prefer as fill colour.

hth
capt.
0
 
LVL 15

Expert Comment

by:Haris Djulic
Comment Utility
Hello,

please check attached file
QC.xlsm
0
 

Author Comment

by:CMILLER
Comment Utility
samo4fun, very nice.

I see in sheet1, if I add additional col's what I need to add.

What do I need to add to the VB if I add more col's to sheet1?
0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
Comment Utility
I changed the code so that you do not need to change VBA code you just add additional columns to sheet MAIN and based on that layout i.e. number of columns the code will transfer than number of columns from SHEET1.

Try it
QC.xlsm
0
 

Author Comment

by:CMILLER
Comment Utility
Looks good. How do I move the button on the sheet?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 15

Expert Comment

by:Haris Djulic
Comment Utility
On the Developer ribbon you click on the design and then you can move the button to desired place. In design mode the code wont run so you need to unclick it to run the code...

If you dont have the developer ribbon then you enable it using these steps : http://msdn.microsoft.com/en-us/library/bb608625.aspx
0
 

Author Comment

by:CMILLER
Comment Utility
Cool, Thanks.
0
 

Author Comment

by:CMILLER
Comment Utility
Sam,

I added some additional cells and it nots checking them.

In Sheet1 (input-Sheet2) I have cells A-DA. I have the code "=+COUNTBLANK(B2:DA2)" in cell DB for all A2-A1098 Name Employee

On the MAIN (Sheet1) it only has correct data for cells A-G, Cells H-DA are all filled with X's, which is not correct.
0
 
LVL 15

Expert Comment

by:Haris Djulic
Comment Utility
Can you add you excel for me to test it?
0
 
LVL 15

Expert Comment

by:Haris Djulic
Comment Utility
Just noticed this row has error so just change

  Set checkrange = ActiveSheet.Range("a" & ActiveCell.Row, "g" & ActiveCell.Row)

to

Set checkrange = ActiveSheet.Range("a" & ActiveCell.Row, Replace(Cells(1, LastColumn).Address(False, False), "1", "") & ActiveCell.Row)
0
 

Author Comment

by:CMILLER
Comment Utility
Its working now with the code change.

Thanks.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Getting information about Fonts being used in a PDF file A colleague of mine recently faced an issue related to the PDF file format. The PDFs were containing mission critical client information, they were successfully mailed but there was a sm…
Have you ever come up with a need of emailing only few pages of PDF file to one of yourfriend or colleague, instead of whole Adobe file? If yes, then surely you have face problems in doing that! Read this section as I have suggested multiple solutio…
In this first video of the three-part Xpdf series, we introduce and describe Xpdf, a library containing nine command line utilities that perform various functions on PDF files. We show where the library is located and how to download it, discuss its…
In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now