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

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.

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.