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
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