Prev: Filling Out Cells Based on Data Validation
Next: formula for averages
From: Nora_GG on 1 Jun 2010 17:54
How can I remove columns that contain all zeros? Is there a macro? I craeted
a sumif formula to flag columns subtotaling zero but I need a method to
remove these columns. Thanks
From: Gary''s Student on 1 Jun 2010 19:45
Try this:

Sub RemoveColumns()
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
If Application.WorksheetFunction.Sum(Columns(i)) = 0 Then
Columns(i).Delete
End If
Next
End Sub

--
Gary''s Student - gsnu201003


"Nora_GG" wrote:

> How can I remove columns that contain all zeros? Is there a macro? I craeted
> a sumif formula to flag columns subtotaling zero but I need a method to
> remove these columns. Thanks
From: Jacob Skaria on 2 Jun 2010 01:09
I would use COUNTIF() instead..

Sub DeleteColumnswithZeros()
Dim lngCol As Long, lngLastCol As Long

lngLastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For lngCol = lngLastCol To 1 Step -1
If WorksheetFunction.CountIf(Columns(lngCol), 0) + _
WorksheetFunction.CountBlank(Columns(lngCol)) = _
Rows.Count Then Columns(lngCol).Delete
Next
End Sub


--
Jacob (MVP - Excel)


"Nora_GG" wrote:

> How can I remove columns that contain all zeros? Is there a macro? I craeted
> a sumif formula to flag columns subtotaling zero but I need a method to
> remove these columns. Thanks
From: Nora_GG on 2 Jun 2010 18:28
Thank you Gary's Student. I am pretty new to building macros. I created the
macro below in a separate workbook and then opened it in the workbook I
needed to apply the macro. Should I have created the macro in the workbook
that needed the columns removed? Also, did I need to include a range in the
macro below or was it ok to copy as is? Appreciate the assistance.

"Gary''s Student" wrote:

> Try this:
>
> Sub RemoveColumns()
> Dim nLastColumn As Long
> Set r = ActiveSheet.UsedRange
> nLastColumn = r.Columns.Count + r.Column - 1
> For i = nLastColumn To 1 Step -1
> If Application.WorksheetFunction.Sum(Columns(i)) = 0 Then
> Columns(i).Delete
> End If
> Next
> End Sub
>
> --
> Gary''s Student - gsnu201003
>
>
> "Nora_GG" wrote:
>
> > How can I remove columns that contain all zeros? Is there a macro? I craeted
> > a sumif formula to flag columns subtotaling zero but I need a method to
> > remove these columns. Thanks
From: Nora_GG on 2 Jun 2010 18:32
Thank you Jacob. I am pretty new to building macros. I created the macro
below in a separate workbook and then opened it in the workbook I needed to
apply the macro. Should I have created the macro in the workbook that needed
the columns removed? Also, did I need to include a range in the macro below
or was it ok to copy as is? Appreciate the assistance.

By the way, I created the macro below by selecting the Macro option under
Tools.

Thanks again.

"Jacob Skaria" wrote:

> I would use COUNTIF() instead..
>
> Sub DeleteColumnswithZeros()
> Dim lngCol As Long, lngLastCol As Long
>
> lngLastCol = ActiveSheet.Cells.Find(What:="*", _
> SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
> For lngCol = lngLastCol To 1 Step -1
> If WorksheetFunction.CountIf(Columns(lngCol), 0) + _
> WorksheetFunction.CountBlank(Columns(lngCol)) = _
> Rows.Count Then Columns(lngCol).Delete
> Next
> End Sub
>
>
> --
> Jacob (MVP - Excel)
>
>
> "Nora_GG" wrote:
>
> > How can I remove columns that contain all zeros? Is there a macro? I craeted
> > a sumif formula to flag columns subtotaling zero but I need a method to
> > remove these columns. Thanks
 |  Next  |  Last
Pages: 1 2 3
Prev: Filling Out Cells Based on Data Validation
Next: formula for averages