DEFINE RANGE IN AN EXCEL 2013 MACRO

Unless I'm misunderstanding your question, you don't need a macro. There's a built-in function in Excel called Average. So to get the Average of A15:A19, you would put this in any cell:

=AVERAGE(A15:19)

It doesn't matter where the cursor is or what cell contains the Average formula. Regards, Joe

Wrong forum?

Yes, wrong Topic, but I wouldn't suggest trying to change it:

https://www.experts-exchange.com/questions/27875154/On-the-Assignment-of-Correct-Topics-to-Questions.html

:)   Regards, Joe

the macro must be adaptable.

the next time i want an average the range may be from x25:x5

it would be cumbersone(and time consuming) to add the new range.

the macro should find the new range automatically

lets try a picture to better explain my needs

2 worksheets in excel 2013

  sheet 1                               sheet2
           A                                     X
 15    10                         145     25
 16      5                         146     17
 17     -50                       147    -9
 18      8                          148    68
 19     10

Note that the listing has a different size AND a different range

Want a macro to calculate the average if i place the cursor below the "10"
Also want to calculate the average if the cursor is below the "68"

What is the criterion for stopping on the way up? Any non-numeric cell? An empty cell? Or should an empty cell count as a zero in the average?

Btw, you should take McKnife's excellent advice and add Topics for MS Excel and VB Script (there is no Topic for VBA, but VB Script should cover it). Also, I suggest replacing the W8 Topic with MS Office. Those three Topics should get more of the right experts in the mix (you're limited to three Topics, although the Topic Advisors can go up to five). To request Topic changes, click the Request Attention button under your question on the right. Regards, Joe

Sorry ... should have included that .

An empty cell terminates the range.  The empty cell is not included in the calculations

Sorry for the delay...left the office for a bit. So what you need is a VBA script/macro that starts at the cell above the active/current cell and loops up the column until it finds an empty cell (or row 1), then calculates the average of all those cells, excluding the active/current cell and the empty cell. I'm sure this will be an easy task for a VBA expert, but that's not me. If you change the Topics to MS Excel, MS Office, and VB Script, I'm confident that an expert will provide you with such a VBA script very quickly. Regards, Joe

Hi Netminder,
That's a good change, but I think you should add VB Script, as that will likely get some VBA experts in the mix. Regards, Joe

So you want a macro that knows the start and end range of the data? Unfortunately, there's very little information in your data above for us to work on here, so perhaps with a few quick questions, we can get this done, together.

I will assume that the data you have above is all that is on the sheet? I'm sure I am wrong but this will get the ball rolling.

Ok, so working on that assumption (that the sheet just has data from say Row 10 to Row 25) then we need the Macro to know this:

We start by working out the first and last rows:

HERE'S THE FIRST ROW CODE:
Worksheets("WORKSHEET_NAME").Select
firstRow = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlNext).Row

HERE'S THE LAST ROW CODE:
lastrow = Worksheets("WORKSHEET_NAME").UsedRange.Row - 1 + Worksheets("WORKSHEET_NAME").UsedRange.Rows.Count

Great, so now we know the last row & the last row.

So we define the range we want to average as such:
aveRange = Worksheets("WORKSHEET_NAME").Range(firstRow & ":" & lastRow)

Now, let's assume you have Excel 2010 upwards, so you can just use worksheetfunction.average against these two now known values:

LIKE:
aveValue = WorksheetFunction.Average(aveRange)

So in short, assuming Exchange 2010 upwards, here's your entire code:

Sub test()
    Worksheets("WORKSHEET_NAME").Select
    firstRow = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlNext).Row
    lastRow = Worksheets("WORKSHEET_NAME").UsedRange.Row - 1 + Worksheets("WORKSHEET_NAME").UsedRange.Rows.Count
    aveRange = Worksheets("WORKSHEET_NAME").Range(firstRow & ":" & lastRow)
    MsgBox "Your average is: " & WorksheetFunction.Average(aveRange)
End Sub

I am sure there's stuff I have assumed wrong but that should get you most of the way home.

I am working in Excel 2010 and I assume the function that I am going to refer to wasn't removed in Excel 2013.

On the Home Tab of the Toolbar I have a group called Editing. In this group there is an option for Autosum with the Sigma symbol alongside it and a dropdown listing which function to add. One option is Average.

When the cursor is in a blank cell beneath (or to the side of) a set of data clicking the button will automatically select all data above the active cell until a blank is found. Confirm with enter.

Does this not do what you want with a built in function rather than relying on VBA?

Thanks
Rob H

Hi Rob,

I can confirm that the AutoSum feature is still in the Editing section of the ribbon in Excel 2013:

And it still has the Average function in the drop-down:

I don't know if this will satisfy radrick60's needs, but at least it's still there in 2013. Good thought! Regards, Joe

The drop down doesnt work .... the range specified while recording the macro stays the same ! Wont work with other locations with different ranges

I want to use the macro on other sheets that have different ranges.

An example may help clarify my goal and problem.


sheet 1data                       sheet2 data

         A                                            E
 5     45                                143   90
 6                                         144
 7     10                                145   17
 8     -9                                 146   -9
 9      5                                 147    22
10    65                                148    zz
11   -50  
12     xx
13
I wrote a macro with the cursor located in xx.

The macro is:  =count(up:CTRL+up then) then enter.
This gives me the count (5) in location xx
up refers to the up arrow

If I use the same macro with the cursor in postion zz, i get a count result of 4 instead of the correct count of 3.  The CTRL+up should take the cursor to row 145 because 144 is a blank, but goes up 5 column slots!??

If you can solve this by recording a macro i would be very happy. Am not very good at programming with VB and would avoid it at all cost

I gave you the exact code above that does what you asked for. Please try it.

Your solution is using VB ( i think)

You suggest i try it .... havent got a clue as to how to "try it"

Once i can establish ranges as  described, the plan is to expand on its use and complexity.

To me, the VB is NOT the solution.... i am not a programmer and am not inclined to start learning VB coding

I need a macro that I can record that eliminates the stated range problem.

In your heart if you believe it's not possible to do this with a recorded macro, so state it.

If you think it might be possible, so state it. As a last resort i will go to Microsoft (ugh)

Thanx for your efforts

When you say the Dropdown doesn't work, are you referring to the AutoSum dropdown?

I was suggesting you use this each time you need it rather than recording a macro using it and for the macro to be repeated.

Each time you click on the AutoSum option it will assess the area to be summed based on the current active cell.

You may be able to split the AutoSum dropdown so that the Average option is a specific button on your QAT. This way your process would be one click and enter; if using AutoSum and Dropdown, your process is two clicks and enter.

Thanks
Rob H

To clarify terminology, in simplest terms a macro is VBA (Visual Basic for Applications).

Ummm.. A Macro *is* VBA, I'm afraid. That code above is *the exact Macro code* you asked for.

1. Open Excel.
2. Press Alt-F11 to open the macro editor / developer.
3. Paste in the code as above and change the worksheet name to match (so instead of where I have "WORKSHEET_NAME" put in "sheet 1" or whatever the sheet is called). If you want it to just use the active sheet, use worksheets.activesheet instead of the name.
4. Press F5 to run the Macro, to test it.

That's it. Job done.

That's exactly what a Macro is. If you record a Macro and look at what's recorded, you'll see it's in VBA.

You asked for a Macro.

Here - this might help.

http://www.hostone.com.au/clients/get_ave.xlsm

Download that file. It has the macro, pre made, with a button to push nice on the screen, so you can see how it works. If you press ALT-F11 with Excel open, you'll see the code, exactly as above in the sheet - and it's the only code in the sheet.

Push the button on the page and you will see the average. Move the numbers around anywhere you want it, within any one column and it will continue to work.

This is exactly the same as a recorded macro, except it doesn't have the limitations of needing a pre-known number of cells.

Incidentally, in Excel 2013, if you simply highlight any cells at all, Excel shows you the average in the bottom green bar, automatically, with nothing further to do. Just like it shows the count and sum in older versions, 2013 also shows the average.

You seemed to have solved the "range problem" with the code you developed.

However, the macro i am trying to create is pretty large and does much more that finding an average over a specific range.

The lack of a solution to the "range problem" has been stopping me from finishing my large recorded macro. If i download your solution , them i must somehow integtrate the remainder of my macro (95%) into your code. This is VBA coding .... which i am not interested getting into.

Again, I want to record a macro that will not have the "range problem". No VB code for me to tinker with. VBA code will exist but it will be transparent to me.

Asking again .... is it possible to solve the range problem via steps in a recorded macro?

Give me your opinion on this question.  If you dont think it is possible,  I will contact microsoft as my last resort.

Intuitively , it seems like establishing a range in a recorded macro should be rather basic.

I am currently using the autoSum to calculate my averages. The problem is that every time my data changes, i must recalculate about 30 averages. Rather boring and time consuming.  A macro would do it in virtually no time.

I tried using the autoSum in a recorded macro, but the number of numbers calculate in the macro remains constant. Shouldnt be .... tha auto summer goes to the first open cell and stops ???

Thanx for your help

To be fair, you only asked in the question for a macro that would handle a dynamic range - if you have additional requirements that you haven't mentioned, no one is really going to know about them.

So, in regards to your question of whether or not it's possible to record a macro that is range independent, the answer is no. Excel unfortunately sucks when it comes to macro recording doing anything other than a verbatim copy of your actions.

So in effect, you're asking if it's possible to perform a complicated (maybe not to a human but complicated to a machine) and dynamic operation without doing any programming at all? We all wish magic like this existed but it doesn't - at least not yet.

If you tell me the *entire* task you're trying to achieve I might be able to come up with another way for you to get what you need or give you a macro to do the lot.

In your bigger picture, how are you locating the block of data to be Averaged?

When the Macro runs do you want the formula placed in the cell or the Average value?

Earlier comment suggests you are locating the end of the range but I think you may be doing this manually. Once the data is located you would be able to use VBA to extract the row and column number of the finish; likewise doing the VBA equivalent to Ctrl + Up as suggested earlier would then give the Row and Column of the start of the range.

With these variables you would then be able to have VBA write the Average formula for you.

If you are wanting just the value pasted in then the calculation can be done within VBA but the location of the data needs to be determined.

So it seems like the broader question that you need answering might be how to locate the data to be Averaged. Once located I suspect determining the range and the calculation of the average will be fairly simple.

Thanks
Rob H

Slight correction to comment by HostOne:

Recording of a macro that is not Range Dependent is possible. When recording macros there are two recording mode options Absolute and Relative.

Using Absolute mode will record specific cell references, if the cursor is in A1 and you move to E5 it will record selection of E5.

In relative mode it will record relative movement, if the cursor is in A1 and you move to E5 it will record movement of +4 columns and +4 rows, therefore if the macro is then run with the cursor in B2 the movement of +4 columns and +4 rows will take the cursor to F6.

There are plenty of other options for non specific references eg replicating the Ctrl + Up or Ctrl + Down keyboard actions.

It is often better to write the code without using specific cell references as it is possible that cell references will change but if hard-coded in the macro the selection of a cell will then be incorrect.

Thanks
Rob H

I have created a good part of my macro with good results. The only problem is that the range of the calculation is FIXED by the macro. It doesnt "see" the blank cell

I did record the macro in relative mode


It doesnt seem like you can help be record the macro without getting into VBA code ...... which i will not.

I think we should close this problem

Please can you copy the section of code created by the recording to us here?

We can then evaluate what seems to be the problem.

Thanks
Rob

OK ..... lets try one more time

I actually set this up on a spreadsheet and performed the steps so indicated


 location 1                    location 2
           C                                    E
 12     -4
 13     54                        25     9
 14                                 26    14
 15    10                         27    
 16      5                         28    20
 17     -50                       29    41
 18      8                         30     -6
 19     10                        31    40
 20     XX                        32     8  
                                       33   62
                                       34   29
                                       35   YY

The first recorded macro I tried included the following steps:

PLACED THE CUSOR AT LOCATION XX
=average(
up
:
CTRL+UP
)
ENTER

UP IS THE PRESSING OF THE UP KEY
The results gave the average of the 5 numbers (c15 to c19)

Placed the cursor at YY and ran the  macro
 Yielded the average of 5 numbers (e34 to e30)  ... should be 7 numbers (e34 eo e28)

Tried a different approach:

Placed tha cursor at location XX
Selected AutoScan then AVERAGE
ENTER

The results gave the average of the 5 numbers (c15 to c19)

Placed the cursor at YY and ran the  macro
 Yielded the average of 5 numbers (e34 to e30)  ... should be 7 numbers (e34 eo e28)

I know you guys are very good with VBA code ... you do it for a living.
I realize it can create good macros and provide flexability.
For numerous reasons, I dont want to get involved with VBA coding

After all, Microsoft created the RECORD MACRO feature for guys like me that didnt want to get involved with the code

I need a solution for this range problem by pressing the correct kets in a RECORD MACRO mode.

There's a reason everyone is saying you need to use VBA. In effect, you keep asking if it's possible to get what you want without putting in the hard yards. It's not. This is like insisting a screwdriver be a chainsaw because you only know how how to use a screwdriver.

The tools you're using have limitations. If you're not willing to work beyond those, you're out of luck. End of story.  If you had simply pasted the code above literally over the macro you recorded, you'd have finished this days ago.

You can get the help you want here if you are capable of being flexible. If not, then the very best of luck to you.

WOW ........ rather curt response

I think you are being inflexible .... its VBA or nothing and my way or the highway
You should think about expanding your scope of expertise.... to accomade your customer needs.

Never answered the question: Is it possible to do this with key strokes in a RECORD MACRO mode? Dont think you know Mr expert.

Anyway, the very best of luck to you also

Radrick60, people here are trying to assist you. For free. Your responses have been bordering on rude for sometime. Your repeated use of all caps is interpreted as shouting. I suggest you reread your responses above and recall the people here are *volunteering* their time exclusively for your benefit and reassess.

Now: can you do this in a recorded macro? No.

*Unless* maybe, just maybe, the cells on the page are the only ones on the page? Then perhaps, you could macro record the selection of the entire page and average it. Excel 2013 is smart enough to know what you mean if you try this and it only averages the cells that actually have something in them. I don't have Excel 2010 to test but this just might work for you. However I doubt you're fortunate enough that only one small group of data exists on a single sheet?

Now, it's not "my way or the highway" and you're not my customer. You're not paying me a cent. You don't *have* to use VBA but people here are.. and this is important... offering to *write it for you*. For free. Without knowing you. If you want to take up the offers of assistance people are giving you, then say the word.

I dont know the "pay" structure, but this service would cost me $20/ month

I assumed the people giving the help would get payed

I appreciate the NO answer related to doing this in a RECORD MACRO mode.

If i had this earlier, would have eliminated all the reponses after that.

Your a little sensitive ..... using caps or underlining is a standard way of emphasing a point.

I think it is appropriate if this case be closed

Anyway, thanx for your effort

As requested earlier, what code did your recorded macro create?

From that we will probably be able to rewrite the code for you to run.

I don't fully understand your objection to VBA. By recording a macro you are creating VBA code. By running that recorded macro you are using VBA code.

I can replicate those keystrokes and create a string of code but cannot upload at the minute, responding on mobile at the minute. However, re-creating the code doesn't ascertain how you are locating the data to be averaged.

Thx
Rob

Having been at the start of this thread (but wanting to turn it over to the VBA experts, which is where I thought the solution would lie), I'd like to jump back in for a moment.

First, a comment for HostOne. You emphasized to Radrick60 that people are trying to help, "For free."

"...you're not my customer."

"You're not paying me a cent."

"...offering to *write it for you*. For free."

The thing is, from Radrick60's perspective, it is not for free. He is (or will be, after his trial) paying $19.95 USD per month for the service. In a way, he is not responsible for the fact that EE's business model is to use free labor, like you and me, rather than paid employees. You and I work for points and t-shirts...and for the satisfaction of helping others...and for learning quite a bit along the way ourselves. But there is a paying customer in this equation...it's just that the money is not going to the experts, like you and me, who provide the solutions. It's an amazing business model, where you get your primary labor force for free (or very close to free), which I've heard described in other EE threads as "gamification" (not a term I was previously familiar with, but based on the Wikipedia definition of it, I'd say that EE has done an awesome job implementing it).

Second, a comment for Radrick60. You said, "After all, Microsoft created the RECORD MACRO feature for guys like me that didnt want to get involved with the code". Yes, but the same Microsoft also created VBA for those cases where the RECORD MACRO feature isn't sufficient, which happens often. VBA is a powerful capability that can provide solutions which are not achievable by recording a macro. For example, in one of your other questions, dustock wrote 33 lines of VBA code to solve your question.

You also said, "I need a solution for this range problem by pressing the correct kets in a RECORD MACRO mode." This may not always be achievable, which appears to be the case in this particular issue.

Regards, Joe

The following script will add an AVERAGE formula in the active cell based on the number of populated cells above it but as per my earlier question, how are you identifying where to put the formula.

Sub SetAverage()
    AveRow = ActiveCell.Row
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.End(xlUp).Select
    FirstRow = ActiveCell.Row
    LastRow = AveRow - 1
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    NumberRows = (LastRow - FirstRow + 1) * -1

    ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & NumberRows & "]C:R[-1]C)"
End Sub

This problem with making the range work in the RECORD MACRO mode is a small part of the macro that i will be using (if the range problem gets resolved)

If I cant get a resolution of the range problem when using the RECORD MACRO feature, then I will not use any macros in my application.

It is simply not in my best interest (time wise) to become VBA literate

So, if you are convinced it is not possible to make it work with the record macro feature then it is best that this project be terminated.

If you truly believe it is not possible I would appreciate you telling me so

Thanx for your effort

Why are you still using the Record Macro feature? The section of the macro has been written for you.

The script above can be incorporated into the Recorded script at the relevant point overwriting what was recorded with the fixed range and then the script can be used as if it had been recorded.

We are not expecting you to become VBA literate. You have recorded the basic script that you require but have an issue with a section creating a fixed range. That section has been rewritten for you.

Please explain what further problems you are now having or what you are trying to do.

Thanks
Rob

Thinking outside the box:

Is your situation one of the following?

1) You are trying to record a macro for future use including creating the average formula within the macro

2) You trying to use the "Average" macro while recording a macro to perform other functions

I think all of us have assumed scenario 1 but in reality you are in scenario 2, hence why you are still using the Record Macro feature.

Please confirm.

Thanks
Rob

I have another approach to getting the VBA code for my macro.

Are you the one i should send it, or is there a "VBA" group?

                                 Tom

Hi Tom,
EE has Topics, not groups. Unfortunately, there is no VBA topic, but there are Topics called:

Visual Basic Classic
Visual Basic.NET
VB Script

By putting these three Topics on your question, you will certainly get to experts with VBA knowledge. Three Topics are the most a member may put on a question, but EE Admins may add more, so you could click the Request Attention button (under the question on the right) and request two more topics (I believe that five is the max, even for Admins). I suggest these:

MS Excel
MS Office

This will enhance your chances of alerting some VBA experts to your question. Regards, Joe

If you raise another question, you should reference this question in it so that other Experts can also see the history. If you have laready raised the question before I post this comment, add a comment to it referencing this question.

Thanks
Rob H