go to special blank cells

[already]

Hi

I have a lot of formulas in (if(xxx;"")) column A. Some of those results in nothing (blank cells). I did a copy > paste special > values to column B. After that I have selected column B > go to special > Blanks. And I received a message that no cells are blank (nothing is selected). I can work around this problem by selecting the cells and do a clear content and so blank cells are found and I can delete them. Why are the cells not recoginized as blank cells in first instance?

Any idea?

Thanks for your help.

Kind regards

Al

 

[pgc01]

P. S.


Remark: before excel 2010 a multi-area range had a limit of 8k areas.

 

[Rick Rothstein]

... and another one:

Sub ReallyEmpty()
 
On Error Resume Next
With Columns("B").SpecialCells(xlCellTypeConstants, xlTextValues)
    .Value = .Value
End With
End Sub

Since Paste Special/Values was used to create the values in Column B, then you do not need SpecialCells in order to make the "" cells there truly blank, this should do the same thing...

Sub ReallyEmpty()
  Columns("B").Value = Columns("B").Value
End Sub

 

[pgc01]

Since Paste Special/Values was used to create the values in Column B, then you do not need SpecialCells in order to make the "" cells there truly blank, this should do the same thing...

Sub ReallyEmpty()
  Columns("B").Value = Columns("B").Value
End Sub

Thank you Rick. You are alsolutely right, your code produces the same result and is much simpler.

 

[Peter_SSs]

Since Paste Special/Values was used to create the values in Column B, then you do not need SpecialCells in order to make the "" cells there truly blank, this should do the same thing...

Sub ReallyEmpty()
  Columns("B").Value = Columns("B").Value
End Sub

And the PasteSpecial/Values could be eliminated with ..

Sub ReallyRmpty()
    Columns("B").Value = Columns("A").Value
End Sub

.. though I'm not recommending doing the whole column as it could (depending on rows used) slow the process considerably.

It is a pity we don't know a bit more about what the OP is really doing but the purpose seems to be to delete these "blank" cells. Not sure if it is entire rows or just cells in the copy to create a new list without blanks.

A couple of other possibilites:

1. We don't know the full formula in column A or the type of data that may result but it is possible that the formula returns "" blanks or numbers. In this case if the desired outcome is to remove the blank rows and retain formulas, there would be no need for a copy to column B and we could basically apply pgc's idea from post #10 (still with the Special Cells limit proviso) directly to the formulas in column A

Sub RemoveEmpty_1()
    Application.ScreenUpdating = False
    On Error Resume Next
    Range("A2", Range("A" & Rows.Count).End(xlUp)) _
        .SpecialCells(xlFormulas, xlTextValues).EntireRow.Delete
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

2. If the object is to produce, in column B, a list of the 'non-blank' values from column A, then a fast way would be:

Sub RemoveEmpty_2()
    Dim lr As Long, i As Long, k As Long
    Dim a, b
   
    lr = Range("A" & Rows.Count).End(xlUp).Row
    ReDim b(1 To lr, 1 To 1)
    a = Range("A1:A" & lr).Value
    For i = 1 To lr
        If Len(a(i, 1)) Then
            k = k + 1
            b(k, 1) = a(i, 1)
        End If
    Next i
    Range("B1:B" & k).Value = b
End Sub

 

[pgc01]

It is a pity we don't know a bit more about what the OP is really doing but the purpose seems to be to delete these "blank" cells. Not sure if it is entire rows or just cells in the copy to create a new list without blanks.

Yes, for ex. if the purpose is to create the list of values in column A excluding the "blank" cells, then no code would be necessary, we could just use the Advanced Filter.