Hi everyone,
I have an Excel workbook that I'm having some trouble with. Here's the formula that is giving me trouble:
=COUNTA('Data'!AB:AB)
In the sheet 'Data', there is a formula in column AB like this:
=IF(D2<>"Part Executed",T2,"")
=If(D3<>"Part Executed",T3,"")
When the COUNTA function tries to count the values in AB, it counts all the cells- even the "blank" ones.
I realized that clearing the contents of the cells will fix this so I tried to use VBA to accomplish this and it's not quite working. Here's my code:
Any help on how to do this would be greatly appreciated!
I have an Excel workbook that I'm having some trouble with. Here's the formula that is giving me trouble:
=COUNTA('Data'!AB:AB)
In the sheet 'Data', there is a formula in column AB like this:
=IF(D2<>"Part Executed",T2,"")
=If(D3<>"Part Executed",T3,"")
When the COUNTA function tries to count the values in AB, it counts all the cells- even the "blank" ones.
I realized that clearing the contents of the cells will fix this so I tried to use VBA to accomplish this and it's not quite working. Here's my code:
Code:
Sub MakeBlank()
'makes "blank" cells actually blank
Sheets("Data").Select
ActiveSheet.Range("$A$1:$AC$50000").AutoFilter Field:=28, Criteria1:="="
ActiveSheet.Range("$A$1:$AC$50000").AutoFilter Field:=25, Criteria1:="="
ActiveSheet.Range("$A$1:$AC$50000").AutoFilter Field:=26, Criteria1:="="
ActiveSheet.Range("$A$1:$AC$50000").AutoFilter Field:=27, Criteria1:="="
ActiveSheet.Range("$A$1:$AC$50000").AutoFilter Field:=29, Criteria1:="="
Range("Y1").Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
ActiveSheet.Range("$A$1:$AC$50000").AutoFilter Field:=28
ActiveSheet.Range("$A$1:$AC$50000").AutoFilter Field:=25
ActiveSheet.Range("$A$1:$AC$50000").AutoFilter Field:=26
ActiveSheet.Range("$A$1:$AC$50000").AutoFilter Field:=27
ActiveSheet.Range("$A$1:$AC$50000").AutoFilter Field:=29
Sheets("Worksheet").Select
End Sub
Any help on how to do this would be greatly appreciated!