Solved

Excel 2007 Count Blank Cells

Posted on 2014-10-22
11
117 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

One of the questions I get asked again and again is how to validate a field value in an AcroForm with a custom validation script. Adobe provided a lot of infrastructure to do that with just a simple script. Let’s take a look at how to do that wit…
Update 21-May-2015: I temporarily removed the source code to make major changes to the program. Regards, Joe In a previous Experts Exchange article, How To Rename-Move a Batch of PDF Files Based on Contents of the Files (http://www.experts-exchan…
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 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) …

808 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