Macro to sum up columns with different amounts of cells

[yodzak]

Hello,
I need to sum up columns of data that have different amounts of cells. So for example from a1 thru a5, I would want the macro to sum that up. Then move over to column B and sum up b1 thru b7, then move to column C and so on.

Thanks for your help...

Yodzak

 

[super]

you could do it with a macro but would it be easier just to use the sum function for the maximum number of cells in your data. Say the largest number of values you have in any column is 10 then you could use

=sum(A1:A10) , = sum(B1:B10) etc...

 

[yodzak]

Thanks Super. I should have said that my data in in one column. a1 thru a6 then a8 thru a22, a26 thru a54. The amount varies. I tried to create a macro to use autosum, since where autosum is used it will stop a the first blank cell. The macro that I recorded keeps summing up the same amount of cells.

Thanks,
Yodzak

 

[super]

<table style="border-collapse: collapse; width: 144pt;" width="192" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="3" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">data</td> <td style="width: 48pt;" width="64">group</td> <td style="width: 48pt;" width="64">sum</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1</td> <td align="right">0</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">2</td> <td align="right">0</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">3</td> <td align="right">0</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">4</td> <td align="right">0</td> <td align="right">10</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">6</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">7</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">8</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">9</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">10</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">11</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">12</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">13</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">14</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">15</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">16</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">17</td> <td align="right">1</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">18</td> <td align="right">1</td> <td align="right">156</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td align="right">2</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">20</td> <td align="right">2</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">21</td> <td align="right">2</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">22</td> <td align="right">2</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">23</td> <td align="right">2</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">24</td> <td align="right">2</td> <td align="right">110</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td align="right">3</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">26</td> <td align="right">3</td> <td>
</td> </tr> </tbody></table>

formulas are as follows and can be dragged all the way down

column B2 column c2

=COUNTIFS($A$2:A2,"") =IF(A3="",SUMIFS($A$2:A2,$B$2:B2,B2),"")

 

[xenou]

you can try to find the new "last row" with this strategy, which is very common on this board. In this formula y is the column number:

LRow = Cells(Rows.Count, y).End(xlUp).Row

Adapting to columns A-J in turn:

Dim LRow As Long, y As Long

For y = 1 to 10
    LRow = Cells(Rows.Count, y).End(xlUp).Row
    rngAddress = Range(Cells(1,y),Cells(LRow,y)).Address
    MsgBox rngAddress
Next y

Does that help?

 

[yodzak]

Thanks for the thread advice xenou. Thanks to xenou and super, I will try both methods.

yodzak