"Blank" cells are being counted as values- clear contents with VBA?

[mcongdon]

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:

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!

 

[Loomah]

hi
first question is - is the data you're returning in T2, T3 etc numeric or text? if it's always numeric just change your formula to =COUNT(etc...)

but i'm guessing you'll realise that already so....
the following will change all occurances of "" in your data range (change the range i used to suit your neeeds)

Sub a()
Dim c As Range, firstAddress As String

With Worksheets(1).Range("g3:g42")
    Set c = .Find("", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.ClearContents
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

could also just use

=COUNTIF('Data'!AB:AB,"<>""")


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mcongdon]

Thanks guys!

Loomah- I tried to use this code, but I'm not really sure how to make it work. I'm not really much of a programmer, i can record macros and clean them up, but I don't know what to do with the bit you provided

xlbo- I tried this and it returns an even larger number than it originally did.

Let me explain a bit more. Here's the code I add the formula with and extend it down:

ActiveCell.FormulaR1C1 = "Originator"
    Range("AB2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-24]<>""Part Executed"",RC[-8],"""")"
    Range("AB2").Select
    Selection.AutoFill Destination:=Range("AB2:AB50000")
    Range("AB2:AB50000").Select

This problem might be easier to fix if you have any suggestions on how to extend a formula down to the last used row of the sheet instead of to the maximum it could possibly be every time?

 

[Loomah]

hi
run my code in exactly the same way you would have run your own

unless he's trying to trick me coz i haven't been around for a while xlbo's formula caounts all cells??

check the faqs for how to identify the last used cell. there are 2 faqs in there written by myself and the illustrious xlbo. tryand utilise the infor there and post back any issues.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

just an after thought, if you are only ever going to look for "Part Executed" then you could adapt the find code to do the whole job for you something like (this untested piece):-

Dim c As Range, firstAddress As String

With Worksheets("your data sheet").Range("d:d")
    Set c = .Find("Part Executed", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            cells(c.row,"ab")=cells(c.row,"t")
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

then there's another formula option

=sum(if(ab:ab<>"",1,0))

entered as an array formula (ctrl+shift+enter)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[mcongdon]

I ended up using VBA to enter an if statement in column AC. If it met the conditions I specified, then it returned a 1, if not, a 0. This way, I could use the sum function to "count" the records matching my specifications. A lot of my problem stems from this being a template that an end user pastes data into and clicks a button to generate a report. The data can be 3 lines long one day and 20,000 the next. It wasn't the prettiest way of doing it, but it got the job done. Thanks for the help guys.

 

[xlbo]

Yeh - my bad - easy formula option would therefore be:

=COUNTA('Data'!AB:AB) - COUNTIF('Data'!AB:AB,"")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question