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?

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?

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?

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?

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?