?
Solved

Excel 2007 Count Blank Cells

Posted on 2014-10-22
11
Medium Priority
?
138 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 31

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 15

Accepted Solution

by:
Haris Djulic earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Can Be Caused By Disabled Services I have encountered a problem viewing PDF files using Adobe Acrobat Reader.  For the longest time, PDFs might launch or might not.  Sometimes they took about 15 minutes to appear after launching them. After som…
In a previous article published here at Experts Exchange, Signature Image with Transparent Background (http://www.experts-exchange.com/Web_Development/Document_Imaging/A_12380-Signature-Image-with-Transparent-Background.html), I explained how to cre…
In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Suggested Courses
Course of the Month12 days, 11 hours left to enroll

777 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