VBA to clear contents of "" values within a range

[Legacy 173085]

I'm working on a number of macros to automate some reports I run at work and have hit another wall!
<style type="text/css">#avg_ls_inline_popup { padding: 0px; overflow: hidden; position: absolute; z-index: 9999; margin-left: 0px; margin-top: 0px; width: 240px; word-wrap: break-word; color: black; font-size: 10px; text-align: left; line-height: 13px; }</style>
So far my macro creates a new sheet and copy/pastes data from the relevant pivot into the tool.

One of the columns is called 'Loaded" - the answer being either "Y" or ""

My problem is I've got a counta subtotal at the bottom and it's picking up the "" in its count - so it's just counting the total number not the total "Y"s which is what I need.

Does anyone know a way I can write it into my code to clear contents of the "" cells?

Any suggestions much appreciated!

 

[cb12]

One way to do this is with the following code adapted as per what cells you want to delete and obviously where you want to repeat it. May be an easier way, I don't know.

Sub ClearCells()
Dim i As Integer

    For i = 1 To Selection.CurrentRegion.Rows.Count - 1
     If ActiveCell.Value = "" Then
        ActiveCell.ClearContents
     ActiveCell.Offset(1, 0).Select
     
     Else: ActiveCell.Offset(1, 0).Select
     End If
    Next i
End Sub

 

[Legacy 173085]

Hi cb12, thanks for this!

Not sure why but this didn't pick up the blank cells - I guess the blanks from the pivot don't show as "" ??

But it doesn't matter i turned your code around so it skips the 'Y's and for else clears contents and that works perfect!

Thanks for your help

 

[cb12]

If you're looking for blank cells then replace "" with Empty.

HTH

 

[Jonmo1]

Perhaps instead of using counta, use

=COUNTIF(A1:A100,"Y")

 

[Legacy 173085]

cb12 - I tried this and it still doesn't work... It doesn't matter cos turning it around works fine, just interesting to know why not if you work it out!

thanks jonmo1 - i have autofilter on so i'm using subtotal counta - but thanks anyway

 

[Jonmo1]

Perhaps have your formulas in the column 'Loaded' return 1 instead of "Y"
Then you can use count instead of counta in the subtotal