macro for selected cells only

[youngEIT]

I have data in spreadsheet in the following format:
Col-1 Col-2 Col-3
1 DA1 1215
2 DA1 3256
3 DA2 38551
4 DA3 151
and so on.....
COl-1 is ID #, Col-2 is drainage area and COl-3 is area (sq.ft)
I can sort these by DA, then subtotal by Col-2 to find out the total area of each item in Col-2. I also came up with a macro which will automate the sort-subtotal in a click of a button. but when i add another row of data, the macro will left that out in the calculation. is there a way to ask the macro to perform the calculation for the selected cells (rows and columns), so that when I add a bunch of extra rows of data into the above list, I can select the new "calculation-area" and then run the macro?
Regards

 

[IRstuff]

Selection.End(xlDown).Select
MsgBox Selection.Row

moves cursor to last item in a CONTIGUOUS section and displays row

TTFN

FAQ731-376

 

[COEngineeer]

Why not just do a cell to do SUM(C:C) ? It will sum infinite rows. No need a macro.

Never, but never question engineer's judgement

 

[zelgar]

It sounds like you need to have a cell defined to count how many records you have. Using the COUNTA function, determine the number of records you have (e.g.,define cell A9 to be =COUNTA(A10:A65536) if your records start in row 10). In your macro, refer back to cell A9 by the following Max = Range("A9")

hope this helps

 

[joerd]

You might want to look into a Pivot Table to accomplish what you want. No need to sort and subtotal, just update the pivot table.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[bltseattle]

Another alternative to consider, you could name each range of cells in the worksheet using Excel's insert>names menu. (Say you call it "my_range"). Excel VBA will recognize names from your worksheet in the procedure.

Then in the macro, instead of select.range("c1:c25") in the macro, you could use select.range(my_range).

Then, when you need to insert a row, make sure you do it in the middle of the range. The cell range referenced by the Name will update automatically, and you won't have to tweak the macro.

I use this technique a lot. Defined Names can be formulas such as "=OFFSET($c$1,0,0,COUNTA($c1:$c1000),1)". (similar to Zelgars approach, but you don't need the dedicated counting-cell on the worksheet).

This approach let's you write the macro once, and makes Excel do the harder part of keeping track of inserted/deleted rows inside of that range.

 

[IRstuff]

One common way to do what bltseattle suggests is to insert the row as the second to the last. You then copy the current last row data into the new row, then copy the new data into the last row. If the data source is consistent, you can even create a macro to do all those operations automatically.

TTFN

FAQ731-376