+ Reply to Thread
Results 1 to 5 of 5

Need Array to include variable column count automatically

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,308

    Need Array to include variable column count automatically

    SUBTOTAL function has to add subtotals to worksheet every time Col C changes.
    Code below works perfectly IF there are five columns.

    Please Login or Register  to view this content.
    Actual sheets can have wildly differing numbers of columns depending on Client. So for client with 24 columns:

    Please Login or Register  to view this content.
    If I set the "last column" as Variable "f", how do I word the Code so the Array includes Cols 1 - F automatically without have to "recode" the columns each time?

    All solutions, suggestions and alternatives welcome as ever

    Ochimus
    Last edited by Ochimus; 12-27-2019 at 12:20 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    503

    Re: Need Array to include variable column count automatically

    I think it will be helpful in your problem.
    https://www.excelforum.com/excel-pro...f-columns.html
    Best Regards,
    Maras.

  3. #3
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,308

    Re: Need Array to include variable column count automatically

    maras,

    Appreciate the prompt suggestion, but it won't solve the problem.

    The Subtotal sheet has to cover an unknown and varying number of columns, depending on the client involved, and clients add, move or delete columns from one month to the next!

    That's why setting "My Array" as lists of specific column numbers is no good.

    I need an approach that finds the last column on a client sheet, defines that as "f", and sets the array as covering "1 - f" automatically.

    Ochimus

  4. #4
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,862

    Re: Need Array to include variable column count automatically

    Hi Ochimus,

    Excellent question. I created a generalized routine that should help get you started.

    The Table containing the Data can be anywhere on the Sheet. The Columns that require SubTotals MUST be contiguous on the right side of the list.

    Generalized Code Needs 3 Unique Items (All Case Insensitive):
    a. Any Cell Value in the Header Row (must be unique on the Sheet) (e.g. 'Store Id').
    b. The Cell Value in the Header Row for the SubTotal Group (must be unique in the Header) (e.g. 'Sport').
    c. The Cell Value in the Header Row for the 1st SubTotal Column (must be unique in the Header) (e.g. 'Jan').

    See the attached file which contains the following code:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    969

    Re: Need Array to include variable column count automatically

    Assuming the number of columns to be included in the array starts from col A & ends in the last column with data, try this:

    Please Login or Register  to view this content.
    If you can use a certain row, say row 1, to determine the number of columns, then you can use "Rows(1)" instead of "Cells":
    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. [SOLVED] Expanding array formula to include another column
    By Coyote_e in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-11-2019, 11:11 AM
  2. Replies: 1
    Last Post: 11-22-2016, 01:54 AM
  3. [SOLVED] automatically include new fiscal year column in totals sum
    By BrotherNeptune in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-26-2015, 03:24 PM
  4. Array to include extra column
    By Kmartin83 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-14-2015, 04:00 AM
  5. [SOLVED] Macro to Insert column and include a VLOOKUP array for entire column
    By bdouglas1011 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-29-2014, 06:28 PM
  6. [SOLVED] Filtering macro, count number of rows and include count in last column.
    By Folshot in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-12-2012, 07:17 AM
  7. [SOLVED] Automatically updating chart to include new column with VBA
    By Gary in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-28-2006, 03:55 PM

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