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?

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?

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?

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?