Hi Guys!
I am facing a problem while selectiing and highlighting the blank cells in a range.
Data is pasted from some other workbook on the workbook where I have to find the blanks cells and highlight them, however there are blanks in that data but I got Run time error 1004: No Cells were found.
I am using below code:
I am not able to attach my sample file.
Please Login or Register to view this content.
Any help will be appreciated.
Thanks
Last edited by jeffreybrown; 11-14-2012 at 03:31 PM. Reason: As per the forum rules, please use code tags...Thanks.
Try this.
Please Login or Register to view this content.
If posting code please use code tags, see here.
Thanks Norie for the quick response.
I don't know how to add code tags and attach my sample workbook so you can find the real problem
However I used your code, still I'm getting the same error on line
With .Range("A2:P" & lr4).SpecialCells(xlCellTypeBlanks)
Thanks
That means there is no blanks.
Do you have any formulas that are returning an empty string?
Actualy when I checked manualy by using autofilter I found blanks in one column but by this code or applying this method (to select blank cells manually and press F5 then click on Special and choose Blanks) got No cells found.
I don't want to do it manually so I tried this code.
Request you to please provide the instructions how to attach my sample workbook, so that it would be easy to understand my issue.
To Attach a File:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.
Now in the attached workbook in Column J "Mail Stop" there are two blanks cells but these cells are not being highlighed by this code.
One more thing this data is pasted from another workbook where we cann't make any changes.
So please help me to identify the blanks by this method of something else.
Thanks
The 2 cells that are 'blank' aren't being seen by Excel as blank.
This could be because the contain an empty string as a result of a formula.
So, is there any solution for this!
Because this data is fetched from an Access table then pasted in workbook. Earliar I was using IF formula to track the blank cells, but file become so heavy by using these formulas because there are 75 columns in this report for which we have to audit each column as per multiple conditions but these colums are, where we have to track only blank cells so I want to automate it.
So Please share if there is a way to track the blank cells, other than this method.
You could create an Access query that returns all the fields and records in the table and replaces any blanks with something like 'Gap'.
Or instead of pasting the table from Access try importing it using the From Access option on the Data tab.
Thanks Norie for your suggetions.
Unfortunatly we have restricted access so that we cann't create any query in Database and we have access of Excel 2003 so there is no option to fatch data from by using "From Access".
However appreciated your assistance.
You can still import data in Excel 2003 (I used to do it in Excel 97).
I can't remember exactly what the correct sequence was but I think it might have been Data>Get (Import?) External Data...
Tha should get you started if you want to import the data rather than paste it.
Thank You Very Much Norie!
It's Done! Appreciate you help.
Thanks a ton.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks