I have a worksheet in which I have parsed a whole lot of data (all in column A)
I have used the TRIM & SUBSTITUTE functions to strip out any unnecessary spaces.
When I attempt to use goto special blank cells then delete excel does not recognise any blanks cells!
If I first scroll through each cell (A1, A2, A3, ....etc) then excel will recognise the blank cells & delete them.
Is there a way around this or am I missing something?
Any assistance appreciated.
Thanks.
Try this one. If they are real blanks, it should delete them.
Please Login or Register to view this content.
delblanks test.xlsxSorry AB your code does not work.Originally Posted by AB33
Try this one. If they are real blanks, it should delete them.
Please Login or Register to view this content.
I get a runtime 1004 error "no blank cells were found"
I have attached a sample from my workbook. As you can see there are blank cells but they are not deleting!
Hi, anrichards22,
maybe you would need to post the code which leads to this situation instead of working on it later on?
Ciao,Please Login or Register to view this content.
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Loop
Please Login or Register to view this content.
No Loop
Please Login or Register to view this content.
Thanks AB problem solved.
Thanks HaHoBe your code works as well.
Hi, anrichards22,
if the worksheet results from any other code I recommend to look over that code as the issue is likely to have itīs origin in that code - I still donīt like to work on something thatīs coming out wrong from other codings (will try to solve it in the first code).
Ciao,
Holger
Holger,Please Login or Register to view this content.
The worksheet is a text document that is 75% html source code which is imported into Excel.
I cannot post actual data as it is confidential.
Do you have an explanation for Excel not recognising the blank cells after I run the find/replace & the trim/substitute functions?
Hi, anrichards22,
sorry, no idea about that behaviour or how to overcome this in the starting code (neither have a sample of a textfile to import nor the complete code). The import of a textfile and text functions for working on the contents should be respnonsible for this result but I canīt show an alternative to what you have (you have a couple of different ways to work around this by now).
Ciao,
Holger
Holger,
The code is what I have posted. Find and replace that's it!
I then manually input the Substitute/Trim formulae.
As I have stated, I cannot post actual data because it is confidential & I assume it would be pointless posting dummy data because it may not give the desired outcome.
Thanks for your solution, I just asked an additional question so as to improve my VBA knowledge.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks