VBA to replace "" cells with true blanks

[garywood84]

I have a macro that I've recorded which copies and uses paste special to insert some data generated by formulae. Some of the cells are set by the formula to be blank, with "". However, those cells are not truly empty. So, on the pasted data, I want to run a further VBA script to find all "" cells and clear their contents. I found this code online, but can't get it to work:

For Each cell in Range("A1:L10003")
     If (cell.value = "") Then cell.ClearContents
Next

Can anyone help me, please?

 

[Rick Rothstein]

It is not clear from your post what is where. You talk about copying data using PasteSpecial but what is it you are copying... values, formulas? Also, you code attempt is processing range A1:L10003... is that your original formulas or your pasted data?

 

[garywood84]

Thanks for replying, Rick.

I have a hidden sheet that contains formulae. The VBA is copying data from that, and pasting it into a visible sheet, using paste special, so only values are copied, rather than the formulae. Some of the formulae on the source sheet return blank cells (e.g if(x=y, "",...)), but it turns out that where x=y, the resulting visually blank cell isn't actually blank, and when it's copied and pasted as a value into the second sheet, that cell isn't blank either. That's why I need the final step in the VBA code: to find cells that have "" pasted as a value, and clear their contents, so that they are truly blank.

Does that make more sense?

 

[Gokhan Aycan]

Would checking for cell.text ="" work? I can't seem to replicate the copied cell.

 

[Alex Blakenburg]

Try this is should be much faster than looping through cells.
Make sure you do some checking the first time to make sure all the data types are behaving.
I tested it on a date column and it seemed to be ok.

Sub RemoveBlanks()

    Dim rngCol As Range
    Dim rngData As Range
   
    Set rngData = Range("A:L")
   
    For Each rngCol In rngData.Columns
       
        On Error Resume Next
        rngCol.TextToColumns Destination:=rngCol, _
                DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
                Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
                FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    Next rngCol

End Sub

 

[Rick Rothstein]

Thanks for replying, Rick.

...so only values are copied, rather than the formulae.

Does executing this either in the Immediate Window or within a macro fix the problem...

Range("A1:L10003").Value = Range("A1:L10003").Value

 

[Alex Blakenburg]

Does executing this either in the Immediate Window or within a macro fix the problem...

Range("A1:L10003").Value = Range("A1:L10003").Value

That worked in my testing and has to be the fastest way.

The only time Text to Columns might still be of benefit is if you only discover you want to get rid of blanks after you have added formulas into the sheet. Text to Columns seems to leave the formulas intact.