macro for selected cells only
macro for selected cells only
(OP)
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
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





RE: macro for selected cells only
MsgBox Selection.Row
moves cursor to last item in a CONTIGUOUS section and displays row
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: macro for selected cells only
Never, but never question engineer's judgement
RE: macro for selected cells only
hope this helps
RE: macro for selected cells only
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: macro for selected cells only
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.
RE: macro for selected cells only
TTFN
FAQ731-376: Eng-Tips.com Forum Policies