Solved

Excel 2007 Count Blank Cells

Posted on 2014-10-22
11
113 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
ID: 40398241
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
ID: 40398267
Hello,

please check attached file
QC.xlsm
0
 

Author Comment

by:CMILLER
ID: 40398441
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40398452
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
ID: 40400131
Looks good. How do I move the button on the sheet?
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40400243
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
ID: 40402344
Cool, Thanks.
0
 

Author Comment

by:CMILLER
ID: 40402525
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
ID: 40402573
Can you add you excel for me to test it?
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40402597
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
ID: 40403042
Its working now with the code change.

Thanks.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Acrobat’s JavaScript is a great tool to extend the application, or to automate recurring tasks. There are several ways a JavaScript can be added to the application or a document (e.g. folder level scripts, validation scripts, event handling scripts,…
*Adobe Acrobat 9 was used for this article. Particular steps may vary depending on software versions. 1. Create a framework of your form in Word, leaving space where you’d ultimately like the Adobe fields to appear.  (Note: I use the blank lines …
In this second video of the Xpdf series, we discuss and demonstrate the PDFimages utility, which, in a single command, is able to extract all the images from a PDF file and save each one in a separate image file (PBM, PPM, or JPG). Download and inst…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

813 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

18 Experts available now in Live!

Get 1:1 Help Now