Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Eliminate unused columns
I have a table of columns of data per week, which I convert to a table of
columns of data per month. This leaves 3 or 4 blank columns between the months. Is there a simple way to consolidate the month columns into a contiguous region, eliminating the unused columns between the month columns? Or... is there a smarter way to do the conversion than sum across an fill down? |
#2
|
|||
|
|||
One approach is to use a macro:
if your empty columns are completely empty you could use this macro. Hit Alt-F11, Click Insert/Module (check the project window to make sure your workbook is the active project before inserting module) and paste this code into the code window. then select the columns that encompass your tables and empty columns and run (you could assign the macro to a button on your toolbar). keep a backup in case this is not what you want Sub DelCol() Dim Col As Range Dim DelRange As Range For Each Col In Application.Intersect(Selection, _ Selection.Parent.UsedRange).Columns If Application.CountA(Col) = 0 Then If DelRange Is Nothing Then Set DelRange = Col Else: Set DelRange = Union(DelRange, Col) End If End If Next Col If Not DelRange Is Nothing Then _ DelRange.EntireColumn.Delete End Sub "jlbreyer" wrote: I have a table of columns of data per week, which I convert to a table of columns of data per month. This leaves 3 or 4 blank columns between the months. Is there a simple way to consolidate the month columns into a contiguous region, eliminating the unused columns between the month columns? Or... is there a smarter way to do the conversion than sum across an fill down? |
#3
|
|||
|
|||
Thanks. I assume from your answer that there is not a simple command
solution to my need, so I'll give you macro a try (on a working copy, of course,) and post back here my results. Thanks jlb "JMB" wrote: One approach is to use a macro: if your empty columns are completely empty you could use this macro. Hit Alt-F11, Click Insert/Module (check the project window to make sure your workbook is the active project before inserting module) and paste this code into the code window. then select the columns that encompass your tables and empty columns and run (you could assign the macro to a button on your toolbar). keep a backup in case this is not what you want Sub DelCol() Dim Col As Range Dim DelRange As Range For Each Col In Application.Intersect(Selection, _ Selection.Parent.UsedRange).Columns If Application.CountA(Col) = 0 Then If DelRange Is Nothing Then Set DelRange = Col Else: Set DelRange = Union(DelRange, Col) End If End If Next Col If Not DelRange Is Nothing Then _ DelRange.EntireColumn.Delete End Sub "jlbreyer" wrote: I have a table of columns of data per week, which I convert to a table of columns of data per month. This leaves 3 or 4 blank columns between the months. Is there a simple way to consolidate the month columns into a contiguous region, eliminating the unused columns between the month columns? Or... is there a smarter way to do the conversion than sum across an fill down? |
#4
|
|||
|
|||
Found an easier way, at least for resonably sized arrays. Select the data in
the columns you want to consolidat by holding the control key and selecting each column. Move to a clear area and past. The result has the selected columns in conticuous array. "jlbreyer" wrote: Thanks. I assume from your answer that there is not a simple command solution to my need, so I'll give you macro a try (on a working copy, of course,) and post back here my results. Thanks jlb "JMB" wrote: One approach is to use a macro: if your empty columns are completely empty you could use this macro. Hit Alt-F11, Click Insert/Module (check the project window to make sure your workbook is the active project before inserting module) and paste this code into the code window. then select the columns that encompass your tables and empty columns and run (you could assign the macro to a button on your toolbar). keep a backup in case this is not what you want Sub DelCol() Dim Col As Range Dim DelRange As Range For Each Col In Application.Intersect(Selection, _ Selection.Parent.UsedRange).Columns If Application.CountA(Col) = 0 Then If DelRange Is Nothing Then Set DelRange = Col Else: Set DelRange = Union(DelRange, Col) End If End If Next Col If Not DelRange Is Nothing Then _ DelRange.EntireColumn.Delete End Sub "jlbreyer" wrote: I have a table of columns of data per week, which I convert to a table of columns of data per month. This leaves 3 or 4 blank columns between the months. Is there a simple way to consolidate the month columns into a contiguous region, eliminating the unused columns between the month columns? Or... is there a smarter way to do the conversion than sum across an fill down? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Columns | Excel Discussion (Misc queries) | |||
can't insert columns between columns | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Columns in Excel will not allow user to click in them | Excel Discussion (Misc queries) | |||
Removing unused or blank rows and columns | Excel Discussion (Misc queries) |