0

I would like to thank everyone for their feedback so far it has helped a great deal. one question that I am grappling with is how to make my column values even so I can do a dynamic subtototal. 

    Column k       Column R  Column Y    Column AF     Column AM      Column AT

       1                2         4           2             3              5
       3                9         7           8             2              4
       2                3         6           3             5              8
       3                          3                         2
                                  5
 TOT   9                14       25          13             12             17

Column k       Column R  Column Y    Column AF     Column AM      Column AT

       1                2         4           2             3              5
       3                9         7           8             2              4
       2                3         6           3             5              8
       3                          3                         2
                                  5
 TOT   9                14       25          13             12             17

on a monthly basis the column values can fluctuate, the question is how do I use VBA to create a dynamic sum based on the column with the most values. 

Dim Rin As Range
Dim Rout As Range
Dim lRa As Long
lRa = Range("i" & Rows.count).End(xlUp).Row
Set Rin = ws.Range("i" & lRa)
Set Rout = ws.Range("I" & lRa)

aCell.Range("I11:P12", "R12:AY12").Copy

Rout.Offset(2, 0).Resize(1, 28).Formula = "=SUBTOTAL(9," & 
Rin.Address(0, 0) & ")"
lR = ws.Range("I" & Rows.count).End(xlUp).Row - 1 ' Finds the last blank   
row
ws.Range("I" & lR).PasteSpecial xlPasteFormats
Solomon Campbell Jr
  • 1
  • 4
  • 1
    I would like to help but I am confused, For example, you mentioned what should happen with " range A4:A7 " but that range isn't visible in your examples. Also I'm not sure how a "company name increases or decreases". Are you just needing two compare two lists and find out which items are missing from one? Further example data would be helpful, such as what you're starting with, and what it needs to look like when you're done. (There's also some helpful question-asking tips [here](https://stackoverflow.com/help/how-to-ask).) – ashleedawg Nov 12 '17 at 06:49
  • Thanks for responding I have re uploaded the images to give you a better idea of how I want the monthly data set spreadsheet to look like. thanks for taking the time to look at my problem. – Solomon Campbell Jr Nov 13 '17 at 02:14
  • QHarr to answer your inquiry 1. the combo box searches for the company identifier from the four worksheets. 2. I don't want to remove any of the rows that have matching values but I do need to insert any new company names that have been identified.3. I realized that removing duplicate values is not the way to go. The best method is to only insert new names into the july range and copy order details into the month range if a match is found. 4 I only want to insert new company names into the july range. 5. the monthly data set is how the data should look once copied from monthly differential. – Solomon Campbell Jr Nov 22 '17 at 03:43
  • Is the column range fixed where you will be adding the subtotal row? – QHarr Dec 21 '17 at 08:05
  • Qharr thanks for the feedback and the code, it is very helpful. The column range is not fixed which is the challenge. because the column range is not fixed the sum calculation needs to be dynamic based on the rows within that column. – Solomon Campbell Jr Dec 22 '17 at 00:59

1 Answers1

2

If you know where your data starts you can use a method such as that given by Shai Rado.

You can't have any entirely empty rows or columns in the range.

You can then feed this lastRow variable into the range address for adding your subtotal formula.

Example: If your data is a continuous set of populated columns starting at Cell D3 the following will get the last used row number in the range of columns:

Option Explicit

Public Sub AddSubTotal()

    Dim lastRow As Long
    Dim lastCol As Long
    Dim firstRow As Long
    Dim rowHeight As Long
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet2")             'change as appropriate

    With ws.Range("D3").CurrentRegion 'change as appropriate
        firstRow = .Rows(1).Row
        lastRow = .Rows(.Rows.Count).Row
        lastCol = .Columns(.Columns.Count).Column
        rowHeight = lastRow - firstRow + 1     
    End With

    With ws
        .Range(.Cells(lastRow + 1, "D"), .Cells(lastRow + 1, lastCol)).FormulaR1C1 = "=SUBTOTAL(9,R[-" & rowHeight & "]C:R[-1]C)"
    End With

End Sub

If you need a different method to find the last used row and last used column there a lots of available resources online including my favourite by Ron De Bruin Find last row, column or last cell. The appropriateness of each method is determined by the shape and properties of your range e.g. no blank columns or rows in range. So choose a method that is right for your data or that can test for different likely cases and apply different methodologies as appropriate.

It is quite difficult to give a definitive answer (and i don't want to code lots of different possible scenarios) to this question without knowing more about the nature and variability of the data. If you familiarise yourself with the methods of finding last row and column you will be able to implement those that suit your needs.

QHarr
  • 83,427
  • 12
  • 54
  • 101