+ Reply to Thread
Results 1 to 2 of 2

Subtotal formula for the rest of the column

  1. #1
    Registered User
    Join Date
    04-21-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    1

    Subtotal formula for the rest of the column

    Hi everyone,

    I have a new job and it has a lot of tasks that involves doing the same thing in excel frequently so I am trying to learn VBA. I was able to get started with it but I got stuck with putting the rest of the subtotal on the rest of the column The file that I am working on has variable amount of rows and columns. Some of the columns has hours that needs to be subtotal at the bottom but some of the columns don't have data in all the rows but they are still needed to be subtotal until the last row. The code that I have so far were able to input the subtotal in the first column correctly since it has hours in all the rows but the rest of the column are getting subtotaled after the last row with data for that column which is not correct since it didn't include everyone else in the table after the last data that has zero/blank hours.



    Here is the code that I have so far.
    Sub Test1()

    Dim lLR As Long
    Dim lLC As Long
    Dim i As Long
    lLR = Cells(Rows.Count, 1).End(xlUp).Row
    lLC = Cells(1, Columns.Count).End(xlToLeft).Column

    Application.ScreenUpdating = False
    'Select Last column's letter
    Columns("K").Select

    Dim LastRow As Long
    Dim OffsetRow As Long
    Dim ColLetter As String
    Dim LastCol As Long

    'Formula will be 1 rows below the LastRow
    OffsetRow = 1

    'Get the selected column's letter
    ColLetter = Split(Cells(1, Selection.Column).Address, "$")(1)

    'Get the last row
    LastRow = Range(ColLetter & Rows.Count).End(xlUp).Row

    'Insert the formula 1 rows below the LastRow
    Range(ColLetter & (LastRow + OffsetRow)).Formula = "=SUBTOTAL(9," & ColLetter & "2:" & ColLetter & LastRow & ")"
    ActiveCell.Select
    Selection.AutoFill Destination:=Range(ColLetter & LastRow : LastCol & LastRow), Type:=xlFillDefault

    Application.ScreenUpdating = True



    End Sub
    Attached Images Attached Images

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Subtotal formula for the rest of the column

    1)
    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


    2) Try
    Please Login or Register  to view this content.
    Or
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VLOOKUP Formula not calculating when copied down the rest of the column
    By Blake Williams in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-08-2017, 06:30 AM
  2. Can't apply Vlookup formula to rest of cells in column
    By dreamer001 in forum Excel General
    Replies: 5
    Last Post: 12-04-2015, 03:53 PM
  3. [SOLVED] Formula to Calculate Subtotal - Count of #'s Column While Avoiding Duplicates
    By swoosh1014 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 10-30-2014, 10:09 AM
  4. Replies: 3
    Last Post: 11-22-2013, 04:39 PM
  5. Select the last row in a column with data nd paste the value in the rest of the column
    By Sheldon Brooks in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-25-2013, 05:33 AM
  6. Replies: 8
    Last Post: 07-31-2012, 09:41 AM
  7. Macro : To find word and Select rest after this cell and delete rest
    By Zortabello in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2010, 08:06 AM

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