×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

macro for selected cells only

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

RE: macro for selected cells only

    Selection.End(xlDown).Select
    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

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

RE: macro for selected cells only

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

RE: macro for selected cells only

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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: macro for selected cells only

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.

RE: macro for selected cells only

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: Eng-Tips.com Forum Policies

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources