Remove columns with all zeros
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
|