Prev: Prevent changing a fraction
Next: Automatic Excel data selection and transfer/copy to another wo
From: Jacob Skaria on 30 Mar 2010 02:34
Try the below

Sub Macro()

Dim lngCol As Long, lngLastCol As Long

lngLastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

For lngCol = lngLastCol To 2 Step -1
If WorksheetFunction.CountIf(Columns(lngCol), ">0") = 0 Then
Columns(lngCol).ClearContents
End If
Next

End Sub


--
Jacob


"Rupster" wrote:

> I have the following layout for several tabs.
>
> Mar-08 $0 $336 $4,808 $2036 $0 $0 $0
> Apr-08 $0 $300 $130 $$0 $0 $0
> May-08 $275 $433 $$0 $0
> Jun-08 $25 $0 $0
> Jul-08 $624
>
> Each month data gets updated and the zeros all move one column to the right.
> I want to delete all the zeros after the cell containing a number. In the
> above example I want to delete all zeros after $2,036 in row 1.Is there a way
> I can delete them automatically without having to do it manually each month.
>
From: Rick Rothstein on 30 Mar 2010 02:59
I can't see the OP's original message (actually, all I see is your
response), so I am using your message to post my reply. I think this single
line of code will do what the OP wants...

ActiveSheet.Cells.Replace 0, "", xlWhole

--
Rick (MVP - Excel)


"Jacob Skaria" <JacobSkaria(a)discussions.microsoft.com> wrote in message
news:243FE165-CBB0-46BF-9EDA-3A979E5E2C2D(a)microsoft.com...
> Try the below
>
> Sub Macro()
>
> Dim lngCol As Long, lngLastCol As Long
>
> lngLastCol = ActiveSheet.Cells.Find(What:="*", _
> SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
>
> For lngCol = lngLastCol To 2 Step -1
> If WorksheetFunction.CountIf(Columns(lngCol), ">0") = 0 Then
> Columns(lngCol).ClearContents
> End If
> Next
>
> End Sub
>
>
> --
> Jacob
>
>
> "Rupster" wrote:
>
>> I have the following layout for several tabs.
>>
>> Mar-08 $0 $336 $4,808 $2036 $0 $0 $0
>> Apr-08 $0 $300 $130 $$0 $0 $0
>> May-08 $275 $433 $$0 $0
>> Jun-08 $25 $0 $0
>> Jul-08 $624
>>
>> Each month data gets updated and the zeros all move one column to the
>> right.
>> I want to delete all the zeros after the cell containing a number. In the
>> above example I want to delete all zeros after $2,036 in row 1.Is there a
>> way
>> I can delete them automatically without having to do it manually each
>> month.
>>
 | 
Pages: 1
Prev: Prevent changing a fraction
Next: Automatic Excel data selection and transfer/copy to another wo