ClearContents of blank cells in a Table Column

[adambc]

I have a Table/Column which contains blank cells as a result of an IF formula eg =IF(x=y, z, "") - but of course the cell is not "really" blank (it still contains the formula)!

I either need a way to make the IF formula make the cell truly blank, or a way to go through the Column (ideally by Column Name) and ClearContents ...

I'm currently doing this by filtering on Blanks, then deleting using the following code - but it is VERY slow ...

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=36, Criteria1:="="
    ActiveSheet.ListObjects("Table1").ListColumns("DateCompleted").DataBodyRange.Select
    Selection.ClearContents
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=36

Can anyone suggest an alternative/more efficient way to do this please?

Thanks ...

 

[Anthony47]

Don't understand why you need to clear those cells; however I an sure that if you add Application.Calculation = xlCalculationManual before your code and Application.Calculation = xlCalculationAutomatic at the end it will be much faster

 

[PeteWright]

make the IF formula make the cell truly blank

That is not possible, because the main concept of Excel is that it returns a value for a formula

a way to go through the Column (ideally by Column Name) and ClearContents

Try this code and rename the Table Name "TableX" and Column Name "[Col 2]":

Sub ClearBlanksInTableColumn()
   Dim CellX As Range, r As Range
   Set r = Range("TableX[Col 2]")
   r.Select
   For Each CellX In r.Cells
      If CellX.Value2 = "" Then CellX.ClearContents
   Next CellX
End Sub

Here is an example table with data for the code above:

Book1.xlsm
	A	B
1	TableX
2	Col 1	Col 2
3	wefgh
4	sedfrg
5	edrfgv
6	wsedrf
7	wsedrf
8	e34rtgzhb
9	asdf	blablabla
10	okjhgt
11	poiuzgb
12	thnbg
13
14	sadiofv
Sheet23

Cell Formulas
Range	Formula
B3:B14	B3	=IF(A3="asdf","blablabla","")

 

[adambc]

That is not possible, because the main concept of Excel is that it returns a value for a formula


Try this code and rename the Table Name "TableX" and Column Name "[Col 2]":

Sub ClearBlanksInTableColumn()
   Dim CellX As Range, r As Range
   Set r = Range("TableX[Col 2]")
   r.Select
   For Each CellX In r.Cells
      If CellX.Value2 = "" Then CellX.ClearContents
   Next CellX
End Sub

Here is an example table with data for the code above:

Book1.xlsm
	A	B
1	TableX
2	Col 1	Col 2
3	wefgh
4	sedfrg
5	edrfgv
6	wsedrf
7	wsedrf
8	e34rtgzhb
9	asdf	blablabla
10	okjhgt
11	poiuzgb
12	thnbg
13
14	sadiofv
Sheet23

Cell Formulas
Range	Formula
B3:B14	B3	=IF(A3="asdf","blablabla","")

@PeteWright

Duh, should have worked that out for myself!!!

Perfect, thank you ...