Prev: Filling Out Cells Based on Data Validation
Next: formula for averages
From: Gord Dibben on 2 Jun 2010 18:45
For a start you would be best off copying and pasting the macro as is to a
general module in the workbook to acted upon.

Otherwise you would have to qualify which workbook has the ActiveSheet.

UsedRange should take care of all columns and rows that Excel sees as being
in use on the active worksheet.


Gord Dibben MS Excel MVP

On Wed, 2 Jun 2010 15:28:24 -0700, Nora_GG
<NoraGG(a)discussions.microsoft.com> wrote:

>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 20:52
Hello. I set up teh macro and it removed teh columns with all zeros. It also
removed columns containing text data. How can I tweak the macro below so it
ignores columns that contain data such as names, job title, etc.

Thanks again for your asisstance.

"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: Jacob Skaria on 3 Jun 2010 01:01
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 13:13
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: Gord Dibben on 3 Jun 2010 18:18
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

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Filling Out Cells Based on Data Validation
Next: formula for averages