Insert text in column A and calculate the average from column B

[Sverre]

I have a sheet of 20000 rows or more. I have a grouped the sheet with one
blank line. In this blank line I want to insert a text in Column A like this;
content in the cell above+data. In addition I want to put in a formula
calculating the average for the group above from column B to column U.
CAn anyone help me with a VBA to do this. May be I have to put in two blank
lines ?

A.........................B............C
May
May
May
Maydata........Average..... Average
June
June
June
Jundata........Average ......Average
April
April
AprilData.....Average....... Average



Jacob Skaria gave me this VBA who works perfectly, but only for the first
blank line. CAn anybody help me with the rest ?
Sub InsertAverages()

Dim lngRow As Long
Dim lngCol As Long
Dim lngStartRow As Long
Dim strCurData As String

lngRow = 1
lngStartRow = lngRow
strCurData = Range("A" & lngRow)

Do While Range("A" & lngRow) <> ""
If strCurData <> Range("A" & lngRow) Then
Rows(lngRow).Insert
Range("A" & lngRow) = strCurData & " Data"
'Insert Averages from ColB to U
For lngCol = 2 To 21
Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" &
lngRow - 1 & "C)"
Next
lngRow = lngRow + 1
lngStartRow = lngRow
strCurData = Range("A" & lngRow)
End If

lngRow = lngRow + 1
Loop

'Handle Last Range
Range("A" & lngRow) = strCurData & " Data"
For lngCol = 2 To 21
Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" &
lngRow - 1 & "C)"
Next

End Sub

 

[Jacob Skaria]

Sverre

The below code works without any blanks...If you review my original post I
have mentioned that the code will insert a a blank line after each section
and do the averages....

So please try with no blank lines in between....

If otherwise post back/..

If this post helps click Yes

 

[Jacob Skaria]

Hi, I have modified the code which identifies the blank rows and populate the
average.....You can have any number of blanks inbetween sections......

Please try and feedback..

Sub InsertAveragesInBlankRows()

Dim lngRow As Long
Dim lngCol As Long
Dim lngStartRow As Long
Dim lngLastRow As Long
Dim strCurData As String

lngRow = 1
lngStartRow = lngRow
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
strCurData = Range("A" & lngRow)

For lngRow = 1 To lngLastRow
If strCurData = "" And Range("A" & lngRow) <> "" Then lngStartRow = lngRow
If strCurData <> Range("A" & lngRow) And strCurData <> "" Then
Range("A" & lngRow) = strCurData & " Data"
'Insert Averages from ColB to U
For lngCol = 2 To 21
Cells(lngRow, lngCol).FormulaR1C1 = _
"=Average(R" & lngStartRow & "C:R" & lngRow - 1 & "C)"
Next
lngRow = lngRow + 1
strCurData = Range("A" & lngRow)
If strCurData <> "" Then lngStartRow = lngRow
End If

Next

'Handle Last Range
Range("A" & lngRow) = strCurData & " Data"
For lngCol = 2 To 21
Cells(lngRow, lngCol).FormulaR1C1 = _
"=Average(R" & lngStartRow & "C:R" & lngRow - 1 & "C)"
Next

End Sub

If this post helps click Yes

 

[Sverre]

Than you very much. Now its working perfect down the sheet. I am very grateful.

Jacob Skaria skrev:

 

[Jacob Skaria]

Welcome..

If this post helps click Yes