Solved

Excel 2007 Count Blank Cells

Posted on 2014-10-22
11
124 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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…
Update 21-May-2015: I temporarily removed the source code and the code snippets to make major changes to the program. Regards, Joe INTRODUCTION This Article is a follow-up to the Article entitled How To Rename-Move a Batch of PDF Files Based o…
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 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…

728 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