If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Remove columns with all zeros
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 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 . |
#12
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|