Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing dynamic ranges
Hi there
I have column of data in blocks of consecutive cells. There are a varying number of rows containing data and varying number of blank rows between the data. I have used the following code to set the average for the last block of data in the column.... Dim rgSumRange As Range Dim rgAverage As Range Cells(Rows.Count, "o").End(xlUp).Offset(0, 0).Select Set rgSumRange = ActiveCell.CurrentRegion.Columns(15) Set rgAverage = rgSumRange.Rows(rgSumRange.Rows.Count + 1) rgAverage.Formula = "=Average(" & rgSumRange.Columns(1).Address(True, False) & ")" rgAverage.Font.Bold = True I need to navigate up the sheet past the empty ros to the next block of data. How do I do that please? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing dynamic ranges
Alan,
Loop through the areas: Sub TryNow() Dim myArea As Range For Each myArea In Range("O:O").SpecialCells(xlCellTypeConstants).Are as With myArea.Cells(myArea.Cells.Count + 1) .Formula = "=AVERAGE(" & myArea.Address & ")" .Font.Bold = True End With Next myArea End Sub HTH, Bernie MS Excel MVP "Alan M" wrote in message ... Hi there I have column of data in blocks of consecutive cells. There are a varying number of rows containing data and varying number of blank rows between the data. I have used the following code to set the average for the last block of data in the column.... Dim rgSumRange As Range Dim rgAverage As Range Cells(Rows.Count, "o").End(xlUp).Offset(0, 0).Select Set rgSumRange = ActiveCell.CurrentRegion.Columns(15) Set rgAverage = rgSumRange.Rows(rgSumRange.Rows.Count + 1) rgAverage.Formula = "=Average(" & rgSumRange.Columns(1).Address(True, False) & ")" rgAverage.Font.Bold = True I need to navigate up the sheet past the empty ros to the next block of data. How do I do that please? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing dynamic ranges
I should have added that this assumes that your data is constants only, not values from formulas.
HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Alan, Loop through the areas: Sub TryNow() Dim myArea As Range For Each myArea In Range("O:O").SpecialCells(xlCellTypeConstants).Are as With myArea.Cells(myArea.Cells.Count + 1) .Formula = "=AVERAGE(" & myArea.Address & ")" .Font.Bold = True End With Next myArea End Sub HTH, Bernie MS Excel MVP "Alan M" wrote in message ... Hi there I have column of data in blocks of consecutive cells. There are a varying number of rows containing data and varying number of blank rows between the data. I have used the following code to set the average for the last block of data in the column.... Dim rgSumRange As Range Dim rgAverage As Range Cells(Rows.Count, "o").End(xlUp).Offset(0, 0).Select Set rgSumRange = ActiveCell.CurrentRegion.Columns(15) Set rgAverage = rgSumRange.Rows(rgSumRange.Rows.Count + 1) rgAverage.Formula = "=Average(" & rgSumRange.Columns(1).Address(True, False) & ")" rgAverage.Font.Bold = True I need to navigate up the sheet past the empty ros to the next block of data. How do I do that please? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing dynamic ranges
Thank you that worked beautifully....
Now sorry to be a nuisance but can you help with this one.... A column of data contains a series of sael prices in ascending order.... I need to seperate them into ranges of prices i.e. 0-4000, 4-6000, 6-8000 etc and then insert three blank lines ebtween each block. I can then run the average procedure you just sent me for each price range block. Thank you "Bernie Deitrick" wrote: I should have added that this assumes that your data is constants only, not values from formulas. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Alan, Loop through the areas: Sub TryNow() Dim myArea As Range For Each myArea In Range("O:O").SpecialCells(xlCellTypeConstants).Are as With myArea.Cells(myArea.Cells.Count + 1) .Formula = "=AVERAGE(" & myArea.Address & ")" .Font.Bold = True End With Next myArea End Sub HTH, Bernie MS Excel MVP "Alan M" wrote in message ... Hi there I have column of data in blocks of consecutive cells. There are a varying number of rows containing data and varying number of blank rows between the data. I have used the following code to set the average for the last block of data in the column.... Dim rgSumRange As Range Dim rgAverage As Range Cells(Rows.Count, "o").End(xlUp).Offset(0, 0).Select Set rgSumRange = ActiveCell.CurrentRegion.Columns(15) Set rgAverage = rgSumRange.Rows(rgSumRange.Rows.Count + 1) rgAverage.Formula = "=Average(" & rgSumRange.Columns(1).Address(True, False) & ")" rgAverage.Font.Bold = True I need to navigate up the sheet past the empty ros to the next block of data. How do I do that please? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing dynamic ranges
Alan,
How about jsut using formulas based on your unsorted values in coloumn O? =SUMIF(O1:O1000,"<=4000")/COUNTIF(O1:O1000,"<=4000") =(SUMIF(O1:O1000,"<=4000")-SUMIF(O1:O1000,"<=6000"))/(COUNTIF(O1:O1000,"<=4000") - COUNTIF(O1:O1000,"<=6000")) =(SUMIF(O1:O1000,"<=6000")-SUMIF(O1:O1000,"<=8000"))/(COUNTIF(O1:O1000,"<=6000") - COUNTIF(O1:O1000,"<=8000")) etc. But if you really want to sort and put in spaces.... Dim i As Long Range("O:O").Sort Range("O1"), xlAscending, header:=xlGuess For i = 4000 To Application.Max(Range("O:O")) Step 2000 Range("O" & Application.Match(i, Range("O:O"))).Offset(1, 0).Resize(3, 1).EntireRow.Insert Next i HTH, Bernie MS Excel MVP "Alan M" wrote in message ... Thank you that worked beautifully.... Now sorry to be a nuisance but can you help with this one.... A column of data contains a series of sael prices in ascending order.... I need to seperate them into ranges of prices i.e. 0-4000, 4-6000, 6-8000 etc and then insert three blank lines ebtween each block. I can then run the average procedure you just sent me for each price range block. Thank you "Bernie Deitrick" wrote: I should have added that this assumes that your data is constants only, not values from formulas. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Alan, Loop through the areas: Sub TryNow() Dim myArea As Range For Each myArea In Range("O:O").SpecialCells(xlCellTypeConstants).Are as With myArea.Cells(myArea.Cells.Count + 1) .Formula = "=AVERAGE(" & myArea.Address & ")" .Font.Bold = True End With Next myArea End Sub HTH, Bernie MS Excel MVP "Alan M" wrote in message ... Hi there I have column of data in blocks of consecutive cells. There are a varying number of rows containing data and varying number of blank rows between the data. I have used the following code to set the average for the last block of data in the column.... Dim rgSumRange As Range Dim rgAverage As Range Cells(Rows.Count, "o").End(xlUp).Offset(0, 0).Select Set rgSumRange = ActiveCell.CurrentRegion.Columns(15) Set rgAverage = rgSumRange.Rows(rgSumRange.Rows.Count + 1) rgAverage.Formula = "=Average(" & rgSumRange.Columns(1).Address(True, False) & ")" rgAverage.Font.Bold = True I need to navigate up the sheet past the empty ros to the next block of data. How do I do that please? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing name range in the same column with Dynamic name ranges | Excel Worksheet Functions | |||
Counting variable ranges and auto-summing variable ranges | Excel Programming | |||
summing ranges | Excel Programming | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
Summing ranges... | Excel Programming |