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?
0
jlbreyer (3)
6/15/2005 6:14:06 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
646 Views

Similar Articles

[PageSpeed] 46

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?
0
jmb (270)
6/16/2005 3:08:02 PM
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?
0
jlbreyer (3)
6/16/2005 8:19:04 PM
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?
0
jlbreyer (3)
6/20/2005 6:16:04 PM
Reply:

Similar Artilces:

Column widths #3
I'm tying to create a form. Can there be different column widths at the top of the worksheet, and then another width at the bottom? you will need to merge columns a|b|c|d|e |merge| | | | | Linda Gearhart wrote: > > I'm tying to create a form. Can there be different column widths at the top of the worksheet, and then another width at the bottom? Linda The column width property is for the entire column, top to bottom, and cannot be changed. You could experiment with Format>Cells>Alignment>Horizontal and choose "center across columns". Another method,...

Total Change column in portfilio view
Hi there, for mutual funds, the TotalChange column in the portfolio view never seems to update for that day. This only happens for mutual funds, not stocks. The updated quotes for those funds download fine however and the +/- change is indidated fine. Any ideas? I've tried removing and re-adding the column and doing a file repair... This is in Money 2006. In microsoft.public.money, Eric wrote: >Hi there, for mutual funds, the TotalChange column in the portfolio view >never seems to update for that day. This only happens for mutual funds, not >stocks. The updated quot...

adding every other column
i need a formula in cell A1 to total cell C1 and everyother column after that i.e. E1,G1 ect i need a formula in cell B1 to total cell D1 and everyother column after that i.e. F1,H1 ect how do i do that? =SUMPRODUCT(MOD(COLUMN(C1:S1),2),C1:S1) in A1 =SUMPRODUCT(MOD(COLUMN(D1:T1)-1,2),D1:T1) in B1 Adjust the ranges to suit. -- David Biddulph "gma" <gma@mircosoft.com> wrote in message news:233B9A5E-149D-4110-92F0-706CFC5CF2BA@microsoft.com... >i need a formula in cell A1 to total cell C1 and everyother column after >that > i.e. E1,G1 ect > i n...

Row and Column Viewing
I have a spreadsheet that I received that only shows A1 through P20. How can I do this on spreadsheets that I have so that I only show the row and columns that have data in them? -- RudeRam ------------------------------------------------------------------------ RudeRam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8818 View this thread: http://www.excelforum.com/showthread.php?threadid=265742 You can hide the unused rows/columns and or reset the scroll area. Try both to see which you like. Private Sub Workbook_Open() Sheets("sheet1").ScrollA...

Get Range object from Cell Row and Column No.
Hi, I'm using Excel 2003, I have a function which I want it to return a range object which points to a cell, the cell is identified by the code which determins which column number and row number make up the Cells RowIndex and ColumnIndex attributes. For some reason I keep receiving an application-defined or object-defined error, my code is below, any advice would be appreciated: Function GetJournalInsertRange(ColumnHeader As String) As Range Dim ColumnNumber As Integer Dim RowNumber As Integer ColumnNumber = 0 For Each header In Range("JournalHdr&q...

Totaling Columns
I need to calc subtotals based on various information with a cell or group of cells. This is how it works: Column A has a heading of CIF (customer info). Column E has a heading for APP type (checking, savings, cd, loans, credit reserve). Column I is for a subtotal of deposits (i.e., checking + savings + cd) and Column J is for a subtotal of loans (i.e., loans + credit reserve) If a customer has 7 CIF records and within that they have 5 checking accounts, 2 savings and 1 cd, I need the deposit total for that; and 2 loans and 1 credit reserve, I need the loan total for that. Keep in mind, ...

Date Elimination
I have a worksheet with mainly dates in column A in the format of '25 Aug 2008'. Is it possible with a macro or similar to delete lines beyond a certain date (2 years hence)? Basically, I'm not interested in data more than 2 years old. This would eliminate a lot of data and make for a more viewable worksheet. try this Sub del_date() ActiveCell.Range("A1").Select Do Until ActiveCell.Value =3D "" dt =3D Date - 730 If ActiveCell.Value <=3D dt Then ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(1, 0).Select End If Loop End ...

Sorting Columns
I have a worksheet that contains data in a format something like: ID# Name Score 1 Bill 24 2 Jack 3 4 Sue 88 The cell contents are the result of formulas referring to other parts of the worksheet and other worksheets in the spreadsheet. The ID# column is always sequential (incluidng the blank line), but the other columns are in any order. I need to sort the columns by descending score ignoring the blank lines, giving: ID# Name Score 4 Sue 88 1 Bill 24 2 Jack 3 Data -> Sort -> Descending insists on pu...

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

NEED HELP
I need help here. This will save me a ton of work if it can be done. want to know if I can place every 4th row into the same column bu different rows. For example: PERSON 1 1500 Ellsworth Ave # 204 Heidelberg PA 15106-3950 -------------------------------------------------------------------------------- PERSON 2 220 Penn Ave Scranton PA 18505 -------------------------------------------------------------------------------- PERSON 3 101 Gibraltar Rd #150 Horsham PA 19044 -------------------------------------------------------------------------------- And get the data to look like this: P...

eliminating duplicates
I need to elimnate duplicates the following script. I believe what is creating the duplicates is the provcont table, there are different provcont ids for each record. Thanks in advance. SELECT PROVCONT.C_FAX, PROVIDER.NAME, PROVIDER.PROV_PATH, PROVIDER.PROV_ID, PROVSTAT.STATUS, PSTATUS.DESCRIPT, PROVCONT.CONTAC_ID FROM PROVCONT INNER JOIN PROVIDER ON PROVCONT.PROV_ID = PROVIDER.PROV_ID INNER JOIN PROVSTAT ON PROVIDER.PROV_ID = PROVSTAT.PROV_ID INNER JOIN PSTATUS ON PROVSTAT.STATUS = PSTATUS.STATUS WHERE (PROVIDER.PROV_PATH = 2) AND ...

Column Headers showing numbers
Hello there, My Excel spreadsheets are opening up with numbers at the top not letters. I'm sure I must have clicked something! Thank you in advance Julie Hi Julie, Tools | Options | General Tab | Uncheck R1C1 Reference Style --- Regards, Norman "Julie" <anonymous@discussions.microsoft.com> wrote in message news:310801c4b04f$ea2980f0$a301280a@phx.gbl... > Hello there, > > My Excel spreadsheets are opening up with numbers at the > top not letters. I'm sure I must have clicked something! > > Thank you in advance > > Julie > > ...

eliminate duplicates
I have a table with 7 different columns of team members on different teams. I need a count of all team members. How do I query seven different columns and eliminate dups? You've discovered yet another problem with denormalized tables. If you have 7 different columns, each containing the same basic information, you have a repeating group. If you can't go back and redesign your tables, the easiest approach would be to create a UNION query that normalizes the data so that it's in only 1 column: SELECT Member1 AS Member FROM MyTable UNION SELECT Member2 AS Member FROM MyTabl...

Column Chart
How do I create a column chart that (as data labels) shows the actual numbers for one of the series and percentages for two other series. Here is the data: A B C=A+B D E=C+D B/C % D/C % 102 64 166 41 207 38,6% 24,7% 104 62 166 42 208 37,3% 25,3% 103 69 172 43 215 40,1% 25,0% 102 61 163 36 199 37,4% 22,1% 100 56 156 30 186 35,9% 19,2% The chart must show the numbers in col...

Sort Data Base By Column
I'd like to sort a data base by column C and have all the associate date in the rest of the columns sort as well. How is that done? Thanks for any help Travelerswa -- travelerswa ----------------------------------------------------------------------- travelersway's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1762 View this thread: http://www.excelforum.com/showthread.php?threadid=57385 travelersway wrote: > I'd like to sort a data base by column C and have all the associated > date in the rest of the columns sort as well. How is that done? >...

wrapping columns
Hi, I have 4 columns of data that are over 1000 rows. I would like to wra those 4 columns so that I can fit 3 sets of the columns on a printe page. Is there a way to do this short of copying and pasting? Thanks, Mega -- mcarringto ----------------------------------------------------------------------- mcarrington's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2164 View this thread: http://www.excelforum.com/showthread.php?threadid=57241 Hi Megan, You can wrap cells by selecting cells (or columns) and using Format, cells, alignment, [x] wrap cells That wil...

Conditional format for complete column
Hi I have the following conditional rule in column Z3 =AE3<(TODAY()-30) I want to apply it for 50 cells more it means from Z3:Z53 using the information from AE3:AE50. Is there a way to do it in just 1 step I don't want to creat 50 rules. I'm using Vista CF automatically assign the formula to all cells within the selection 1. Select the cell/Range Z3:Z53. Please note that the row reference 3 mentioned in the formula is the active row in the selection. Active cell in the active row will have a white background even after selection 2. From menu Format>Conditi...

Sum of columns until you a certain amount
I have cell A1 which equals 12000. I want to add columns A2 through P2 until it equals the total in cell A1. Once the total would be met it would zero out the remainder. How do I do that? Thanks for the help! And =MIN(A1,SUM(A2:P2) -- HTH RP (remove nothere from the email address if mailing direct) "Andman" <Andman@discussions.microsoft.com> wrote in message news:6FEF94CD-8F6F-4CAA-B17E-1F9472CE580E@microsoft.com... > I have cell A1 which equals 12000. > > I want to add columns A2 through P2 until it equals the total in cell A1. > > Once the total ...

eliminate message from append query to eliminate duplicate record.
Create a new query in design view. Add the two tables for which you wish to compare. When you see the two tables in the design view of the query click on one of your 2 join fields and drag to the corresponding join field in the other table (a line should appear) then double click on fields to add them to the query. For your comparison field Your going to have to explain how you want to compare the two fields. What type of field is in table B, and what kind of comparison do you want to do? You could start with something simple like entering "ComparisonField:Iif(tableA.field1=tableB.field2,...

Replace Columns
Hi, I need some help. I need to replace an unsorted, unfiltered column from one worksheet with a column from a second worksheet. My problem is that I need to keep the rest of the columns in the worksheet exactly the same. Is there a way I can link, group, or associate (not sure of the precise word) two or more columns? In order to understand the spreadsheet, the order must not change yet I have to replace the contents with unfiltered content. Is there an easy way to do this? For example, I always want A1 to be associated with C1, D1, E1, F1, G1. Thanks in advance! In the cell...

eliminating hyper links
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel How do I get word not to link when I make reference (footnote in a book) preceded by http: etc? Daniel_Barnett@officeformac.com wrote: > Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel > How do I get word not to link when I make reference (footnote in a book) > preceded by http: etc? From Word > Preferences choose the AutoCorrect button. Click the AutoFormat as you Type tab. Uncheck Internet and network paths and hyperlinks Click OK There's more about this on the fo...

No unused Payables vouchers can be found
Hello everyone, Using GP9 sp1 I am trying to post a Receivings Trx batch but get "No unused Payables vouchers can be found". Does anyone have any pointers for this? I *greatly* appreciate any help. Thanks, Bryce Sovled it. Turns out the Next Voucher # needed to be incremented by 1,062 (yes, it was that far off from what was in the database). To get to the Next Voucher #: Tools -> Setup -> Purchasing -> Payables -> Options, field "Next Voucher Number". bd wrote: > Hello everyone, > > Using GP9 sp1 I am trying to post a Receivings Trx batch but ...

Excel Rows and Columns Issue
I am an excel 2002 user, and I am having problem putting in th formulas. The formulas are simple, average, add, subtract, an multiplying cells, but the real problem is in "viewing" the rows an columns. Normally, in excel, rows are numbered (1,2,3,4...) and columns ar alphabatized (A,B,C,D...) In my case, both columns and rows ar "numbered" , so putting in a formula is a pain in my neck. How d resolve or change the view of the excel sheet so it displays th "Letters" on the top columns part of the sheet going from left t right, and the "numbers" rows (wh...

Column Headers/headings
Hi, Is there any way that you can get the top row to stay visible no matter where you are on the spread sheet - ie: to be able to see what columns you are typing in (eg cost) when further down the spread sheet. Thanks for any help, Matt. Click on A2 Click Window > Freeze Panes -- Rgds Max xl 97 ---------------------------------------------------- Use xdemechanik <at>yahoo<dot>com for email ----------------------------------------------------- "The Stoat" <thefantstoat@lycos.co.uk> wrote in message news:5iSNb.3831$pD3.3270@news-binary.blueyonder.co.uk... &...

Total cells of one column based on the values in another column?
One of the sheets in my ledger workbook is for annual cash receipts. Column D is for the amount (formatted to number-accounting)and Column E is for who it's from (formatted to number-text). I've got my cells elsewhere for month, quarter, & year totals. The problem is I have to manually, with my adding machine, go thru and add up the total from each source. (How much total income for the year is/was from Co.A, Co.B, Co.C, etc..) I've figured out how to use conditional formatting so that if the text in Column E cells is say, Co.A, the text will be red, if Co.B, text will ...