ClearContents

[dparteka]

I seem to know just enough to not get an error on something that doesn’t seem work.

I’m looking for the macro to… look in cell A18 then if the cell is <=0 then ClearContents of cells A thru V... then look in cell A19 and so on.

Dim MyRange As Range, UsedInRange As Integer
UsedInRange = WorksheetFunction.CountA(Columns("A:A"))
Set MyRange = Range("A18:V1018")
If UsedInRange <= 0 Then
MyRange.ClearContents
End If

I appreciate any help you can offer.
Thanks… Dennis

 

[Smallman]

Hi dparteka

Perhaps attack all of the cells in one hit.

Option Explicit
 
Sub Testo()
    Range("A17", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "<=0"
    Range("A18", Range("V" & Rows.Count).End(xlUp)).ClearContents
    [a17].AutoFilter
End Sub

Take care

Smallman

 

[dparteka]

Hi there Smallman,

Looks like I made a bit of a mistake, I said "if the cell is <=0", this is wrong, it should be if cell A is blank or =0... sorry about that. Also a question, in the first line of your code should A17 be A18?

Thank for the help... Dennis

 

[Smallman]

Hi Dparteka

This bit:

Also a question, in the first line of your code should A17 be A18?

Not at all. You did not supply a file and one would assume if you are starting your criteria search in Row 18 that your headings are in Row 17. If I was to apply the coding from Row 18 the first item would be left out of the criteria so move back one.

I am not going to give you the answer for the change you will need to make. If items to be filtered are <=0 then using simple logic what do you think the criteria would be for items which are equal to zero?

The idea is to get you to come up with the answer so you think about it.

Of course get back in touch if the answer alludes.

Take care

Smallman

 

[dparteka]

I did finally figure it out. Just a note, the reason I asked about changing the <=0 was because I was stuck on how to go about changing it to be =0 and/or blank. You are correct that the =0 part is logically, I was not sure how to incorporate the blank part... thanks for the help, Dennis