Prev: Filling Out Cells Based on Data Validation
Next: formula for averages
From: Nora_GG on 3 Jun 2010 19:46
My apologies Gord Dibben.

"Gord Dibben" wrote:

> Gord Dibben never suggested a macro.
>
> Gord told you where to store the macro that Gary's Student had posted for
> you.
>
>
> Gord Dibben MS Excel MVP
>
> On Thu, 3 Jun 2010 10:13:51 -0700, Nora_GG
> <NoraGG(a)discussions.microsoft.com> wrote:
>
> >Thanks again Jacob. I applied the macro below and it didn't do anything. I
> >also applied the macro Gord Dibben suggested and it removed all columns
> >totaling zero. The only problem is that it also removed columns containing
> >text such as Names, Job Title, etc.
> >
> >Can you suggest how I can tweak the macro below so it disregards columns
> >with text and dates and looks at only the numbers?
> >
> >Again really apperciate the assistance.
> >
> >
> >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
> >
> >
> >"Jacob Skaria" wrote:
> >
> >> If you are new to macros..
> >>
> >> --Set the Security level to low/medium in (Tools|Macro|Security).
> >> --From workbook launch VBE using short-key Alt+F11.
> >> --From menu 'Insert' a module and paste the below code.
> >> --Get back to Workbook.
> >> --Run macro from Tools|Macro|Run <selected macro()>
> >>
> >>
> >> --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?
> >>
> >> No need; the workbook in which you need to delete the columns should be the
> >> active workbook..
> >>
> >> --Also, did I need to include a range in the macro below or was it ok to
> >> copy as is? Appreciate the assistance.
> >>
> >> No need; the macro identifies the last column with values and check for zero
> >> cells upto that column.
> >>
> >> --
> >> Jacob (MVP - Excel)
> >>
> >>
> >> "Nora_GG" wrote:
> >>
> >> > 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
>
> .
>
From: Nora_GG on 3 Jun 2010 19:49
Hi Jacob, I received additional assistance from Gary's Student and was able
to tweak the macro to provide me with the desired results. Thank you again.

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



"Jacob Skaria" wrote:

> If you are new to macros..
>
> --Set the Security level to low/medium in (Tools|Macro|Security).
> --From workbook launch VBE using short-key Alt+F11.
> --From menu 'Insert' a module and paste the below code.
> --Get back to Workbook.
> --Run macro from Tools|Macro|Run <selected macro()>
>
>
> --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?
>
> No need; the workbook in which you need to delete the columns should be the
> active workbook..
>
> --Also, did I need to include a range in the macro below or was it ok to
> copy as is? Appreciate the assistance.
>
> No need; the macro identifies the last column with values and check for zero
> cells upto that column.
>
> --
> Jacob (MVP - Excel)
>
>
> "Nora_GG" wrote:
>
> > 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
First  |  Prev  | 
Pages: 1 2 3
Prev: Filling Out Cells Based on Data Validation
Next: formula for averages