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?

Alan,

Loop through the areas:

Sub TryNow()
Dim myArea As Range
For Each myArea In Range("O:O").SpecialCells(xlCellTypeConstants).Areas
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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

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
news:[email protected]...
> Alan,
>
> Loop through the areas:
>
> Sub TryNow()
> Dim myArea As Range
> For Each myArea In Range("O:O").SpecialCells(xlCellTypeConstants).Areas
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>
>
>

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
> news:[email protected]...
> > Alan,
> >
> > Loop through the areas:
> >
> > Sub TryNow()
> > Dim myArea As Range
> > For Each myArea In Range("O:O").SpecialCells(xlCellTypeConstants).Areas
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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?
> >>
> >
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>> news:[email protected]...
>> > Alan,
>> >
>> > Loop through the areas:
>> >
>> > Sub TryNow()
>> > Dim myArea As Range
>> > For Each myArea In Range("O:O").SpecialCells(xlCellTypeConstants).Areas
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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?
>> >>
>> >
>> >
>>
>>
>>