+ Reply to Thread
Results 1 to 6 of 6

Move subtotals into new column

  1. #1
    Registered User
    Join Date
    10-19-2012
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Question Move subtotals into new column

    I ran the subtotal function, and it spat out all of the subtotals based on my criteria that was specified into the cell directly underneath the set criteria that I told it to...basically like it is supposed to do. Any way, I would like to keep the subtotal fields for the data that is being added up, but would like to move it to the column directly to the right. I ran an if function and got what I wanted, the problem is that I need to hide all of the cells that the if function is pulling from, which will hinder my ability to effectively add data in rows that need data added to them. It is quite a large spreadsheet so I would prefer no to cut and paste each item to the cell I want it in. Is there a way to move each subtotal one to the right and then one up so I can delete unnecessary rows? My subtotals are in column I4 and I would like them to be in J4, etc... I hope that makes sense, thanks in advance for any advice.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Move subtotals into new column

    Maybe you can add an excel example of your data, without confidentional information.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    10-19-2012
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Move subtotals into new column

    Here is an example...the yellow highlights are what I am trying to move from -> to...I want to move the subtotal to where the if function is...and then remove all rows to make it clean.
    Last edited by Ozzanif; 10-19-2012 at 05:08 PM.

  4. #4
    Registered User
    Join Date
    10-19-2012
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Move subtotals into new column

    Answered via google search for macro. "Subtotal move to right". Thanks

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Move subtotals into new column

    Can you post the code?

    I'm also interested in it.

  6. #6
    Registered User
    Join Date
    10-19-2012
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Move subtotals into new column

    Here is what I used...I got it from this article http://excelribbon.tips.net/T008643_...Subtotals.html

    Dim rCell As Range
    Dim rng As Range
    Dim iCol As Integer
    Dim iOffset As Integer

    iCol = 9 '19 is Column I
    iOffset = 1 'Positives go right, negatives go left
    Set rng = Intersect(Selection.CurrentRegion, Columns(iCol))
    For Each rCell In rng
    If InStr(rCell.Formula, "SUBTOTAL") Then
    rCell.Offset(-1, iOffset).Formula = _
    rCell.Formula
    rCell.ClearContents
    End If
    Next

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1