# Thread: Macro to compute average till blank cell appears in adjacent column

1. ## Macro to compute average till blank cell appears in adjacent column

Hi All,

I am newbie to macros. There are 2 columns in my excel sheet. As part of the bigger project, I need to calculate the average% of below % till a blank cell appears in ID column. I am attaching an excel sheet with dummy data to explain better. Really appreciable if any excel champ could be able to provide a macro to do this. Thank you very much in Advance.

Best Regards,
Abhishek

2. Abhishek,

This should do what you want:

Code:
```Option Explicit

Sub MyPercents()

Dim lLastRow   As Long
Dim lNextBlank As Long

lLastRow = Cells(Rows.Count, 1).End(xlUp).Row  '*** Find last used row

lNextBlank = Cells(lLastRow, 1).End(xlUp).Row  '*** Find row Before blank!

If lNextBlank = 1 Then Exit Sub

Do

Cells(lNextBlank - 1, 2).Formula = "=Average(" & _
Cells(lNextBlank, 2).Address(, , xlA1) & ":" & _
Cells(lLastRow, 2).Address(, , xlA1) & ")"

lLastRow = lNextBlank - 2     '*** Move up to next non-blank entry
lNextBlank = Cells(lLastRow, 1).End(xlUp).Row  '*** Get new top row
Loop Until (lNextBlank = 1)

End Sub    'MyPercents```
HTH

3. Many Thanks for your response. I believe I failed to mention that there are multiple columns between A & B. Also I have to update the data from row number 25. There are some other stuff above row 25 which I need not to update.

Request if you modify the macro accordingly. Sorry for the inconvenience caused. Thanks a lot.

Best Regards,
Abhishek

4. Hi Abhishek

You could get what you need using a named formula.
In the attached example file, the named formula is myAvg
Just put
=myAvg
to get the result.

To see the formula definition, look in the Name Manager.
I assumed that there are up to a max of 100 rows to be included for any calculation of the average. You can adjust the formula definition o match your requirements.
You can insert columns and rows etc etc, and the calculated result will adjust etc etc etc

zeddy

5. Thanks Zeddy for your reply. Its working in the Dummy Data Sheet. But once I put the myAvg formula into my excel sheet, it is opening up the window to update the values. If I cancel it, it is showing the error N/A.

Best Regards,
Abhishek

6. Thanks Zeddy. It is working perfectly fine. My mistake.

Best Regards,
Abhishek

7. Hi Abhishek

I am very pleased it worked for you.
Amazing.

..and thanks again to RG for a macro way of doing it.

zeddy

8. Abhishek,

Here's the modified code:

Code:
```Option Explicit

Sub MyPercents()

Dim lLastRow    As Long
Dim lNextBlank  As Long
Dim lStopHere   As Long
Dim lDataCol    As Long
Dim lFormulaCol As Long

lStopHere = 25   '*** Don't process above this line
lDataCol = 1     '*** Column with the raw Data to be averaged
lFormulaCol = 8  '*** Column to contain the Formula

'**** Initialize the variables ****
lLastRow = Cells(Rows.Count, 1).End(xlUp).Row  '*** Find last used row
lNextBlank = Cells(lLastRow, 1).End(xlUp).Row  '*** Find row Before blank!

If lNextBlank < -lStopHere Then Exit Sub  '**** Nothing to Process

Do

Cells(lNextBlank - 1, lFormulaCol).Formula = "=Average(" & _
Cells(lNextBlank, lFormulaCol).Address(, , xlA1) & ":" & _
Cells(lLastRow, lFormulaCol).Address(, , xlA1) & ")"

lLastRow = lNextBlank - 2     '*** Move up to next non-blank entry
lNextBlank = Cells(lLastRow, lDataCol).End(xlUp).Row  '*** Get new top row
Loop Until ((lNextBlank <= lStopHere) Or (Cells(lNextBlank, lDataCol).Value = "ID"))

End Sub    'MyPercents```
Sample Run:

Percents.PNG

Of course you'll have to adjust the Setup Values to fit your actual data but this is not very easy to modify.

HTH

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•