Dear volunteers,
I got following script from the discussion group months ago to separate
groups of data with two empty rows base on cells in column A. Now I would
like to add text and formula in column D and E (in every first empty row) at
bottom of each groups, any ideahow can I do that?

Dim lngRow As Long
For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).EntireRow.Resize(2).Insert
End If
Next

Regards

Hi

Maybe something like this. I guess the approach for the formulas has to be
changed a bit once we know which formulas you need:

Dim lngRow As Long
For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).EntireRow.Resize(2).Insert
End If
Range("D" & lngRow) = "Total"
Range("E" & lngRow).FormulaR1C1 = "=sum(r[-5]c:r[-1]c)"
Next

Regards,
Per

"Seeker" <(E-Mail Removed)> skrev i meddelelsen
news:0CA04C62-F84C-4A05-B660-(E-Mail Removed)...
> Dear volunteers,
> I got following script from the discussion group months ago to separate
> groups of data with two empty rows base on cells in column A. Now I would
> like to add text and formula in column D and E (in every first empty row)
> at
> bottom of each groups, any ideahow can I do that?
>
> Dim lngRow As Long
> For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
> If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
> Rows(lngRow).EntireRow.Resize(2).Insert
> End If
> Next
>
> Regards

Hi Jessen,
Thanks for your prompt reply. If my group contain one row only, then it is
fine, but if more than one row, data in cells of column D & E of the second
and onward rows will also changed to Total and the formula, how can I fix it
please?

"Per Jessen" wrote:

> Hi
>
> Maybe something like this. I guess the approach for the formulas has to be
> changed a bit once we know which formulas you need:
>
> Dim lngRow As Long
> For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
> If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
> Rows(lngRow).EntireRow.Resize(2).Insert
> End If
> Range("D" & lngRow) = "Total"
> Range("E" & lngRow).FormulaR1C1 = "=sum(r[-5]c:r[-1]c)"
> Next
>
> Regards,
> Per
>
> "Seeker" <(E-Mail Removed)> skrev i meddelelsen
> news:0CA04C62-F84C-4A05-B660-(E-Mail Removed)...
> > Dear volunteers,
> > I got following script from the discussion group months ago to separate
> > groups of data with two empty rows base on cells in column A. Now I would
> > like to add text and formula in column D and E (in every first empty row)
> > at
> > bottom of each groups, any ideahow can I do that?
> >
> > Dim lngRow As Long
> > For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
> > If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
> > Rows(lngRow).EntireRow.Resize(2).Insert
> > End If
> > Next
> >
> > Regards
>
>

Try the below

Sub Macro()
Dim lngRow As Long, lngLastRow As Long
lngLastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
For lngRow = lngLastRow To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then _
Rows(lngRow).EntireRow.Resize(2).Insert
If Range("A" & lngRow) = "" Then
Range("D" & lngRow) = "text"
Range("E" & lngRow).Formula = "=today()"
End If
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Seeker" wrote:

> Dear volunteers,
> I got following script from the discussion group months ago to separate
> groups of data with two empty rows base on cells in column A. Now I would
> like to add text and formula in column D and E (in every first empty row) at
> bottom of each groups, any ideahow can I do that?
>
> Dim lngRow As Long
> For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
> If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
> Rows(lngRow).EntireRow.Resize(2).Insert
> End If
> Next
>
> Regards

Hi Jacob,
I just need to add one more End If, then it works perfectly, Thanks again
for both of you guys.
Best Regards

"Jacob Skaria" wrote:

> Try the below
>
> Sub Macro()
> Dim lngRow As Long, lngLastRow As Long
> lngLastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
> For lngRow = lngLastRow To 2 Step -1
> If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then _
> Rows(lngRow).EntireRow.Resize(2).Insert
> If Range("A" & lngRow) = "" Then
> Range("D" & lngRow) = "text"
> Range("E" & lngRow).Formula = "=today()"
> End If
> Next
> End Sub
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Seeker" wrote:
>
> > Dear volunteers,
> > I got following script from the discussion group months ago to separate
> > groups of data with two empty rows base on cells in column A. Now I would
> > like to add text and formula in column D and E (in every first empty row) at
> > bottom of each groups, any ideahow can I do that?
> >
> > Dim lngRow As Long
> > For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
> > If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
> > Rows(lngRow).EntireRow.Resize(2).Insert
> > End If
> > Next
> >
> > Regards

Hi Jacob,
What if I would like to add formula of =sum() in column N and O, how should
do I decide the dynamic range of each group on the start and end rows please?
Regards

"Jacob Skaria" wrote:

> Try the below
>
> Sub Macro()
> Dim lngRow As Long, lngLastRow As Long
> lngLastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
> For lngRow = lngLastRow To 2 Step -1
> If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then _
> Rows(lngRow).EntireRow.Resize(2).Insert
> If Range("A" & lngRow) = "" Then
> Range("D" & lngRow) = "text"
> Range("E" & lngRow).Formula = "=today()"
> End If
> Next
> End Sub
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Seeker" wrote:
>
> > Dear volunteers,
> > I got following script from the discussion group months ago to separate
> > groups of data with two empty rows base on cells in column A. Now I would
> > like to add text and formula in column D and E (in every first empty row) at
> > bottom of each groups, any ideahow can I do that?
> >
> > Dim lngRow As Long
> > For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
> > If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
> > Rows(lngRow).EntireRow.Resize(2).Insert
> > End If
> > Next
> >
> > Regards

Hi

This should do it. You can just change the column reference to put formulas
in other columns.

Sub aaa()
Dim lngRow As Long
Range("A1").End(xlDown).Offset(1, 3) = "Total"
For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).EntireRow.Resize(2).Insert
Range("D" & lngRow) = "Total"
End If
Next
lngRow = Range("D" & Rows.Count).End(xlUp).Row
FirstRow = 2
For r = 2 To lngRow
If Range("D" & r) = "Total" Then
Range("E" & r).Formula = "=sum(E" & FirstRow & ":E" & r - 1 & ")"
FirstRow = r + 2
End If
Next
End Sub

Regards,
Per

"Seeker" <(E-Mail Removed)> skrev i meddelelsen
news:96BA2673-C52C-4836-BD5F-(E-Mail Removed)...
> Hi Jessen,
> Thanks for your prompt reply. If my group contain one row only, then it is
> fine, but if more than one row, data in cells of column D & E of the
> second
> and onward rows will also changed to Total and the formula, how can I fix
> it
> please?
>
> "Per Jessen" wrote:
>
>> Hi
>>
>> Maybe something like this. I guess the approach for the formulas has to
>> be
>> changed a bit once we know which formulas you need:
>>
>> Dim lngRow As Long
>> For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
>> If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
>> Rows(lngRow).EntireRow.Resize(2).Insert
>> End If
>> Range("D" & lngRow) = "Total"
>> Range("E" & lngRow).FormulaR1C1 = "=sum(r[-5]c:r[-1]c)"
>> Next
>>
>> Regards,
>> Per
>>
>> "Seeker" <(E-Mail Removed)> skrev i meddelelsen
>> news:0CA04C62-F84C-4A05-B660-(E-Mail Removed)...
>> > Dear volunteers,
>> > I got following script from the discussion group months ago to separate
>> > groups of data with two empty rows base on cells in column A. Now I
>> > would
>> > like to add text and formula in column D and E (in every first empty
>> > row)
>> > at
>> > bottom of each groups, any ideahow can I do that?
>> >
>> > Dim lngRow As Long
>> > For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
>> > If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
>> > Rows(lngRow).EntireRow.Resize(2).Insert
>> > End If
>> > Next
>> >
>> > Regards
>>
>>

Hi Per Jessen,
Thank you very much, your codes works.
Regards

"Per Jessen" wrote:

> Hi
>
> This should do it. You can just change the column reference to put formulas
> in other columns.
>
> Sub aaa()
> Dim lngRow As Long
> Range("A1").End(xlDown).Offset(1, 3) = "Total"
> For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
> If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
> Rows(lngRow).EntireRow.Resize(2).Insert
> Range("D" & lngRow) = "Total"
> End If
> Next
> lngRow = Range("D" & Rows.Count).End(xlUp).Row
> FirstRow = 2
> For r = 2 To lngRow
> If Range("D" & r) = "Total" Then
> Range("E" & r).Formula = "=sum(E" & FirstRow & ":E" & r - 1 & ")"
> FirstRow = r + 2
> End If
> Next
> End Sub
>
> Regards,
> Per
>
> "Seeker" <(E-Mail Removed)> skrev i meddelelsen
> news:96BA2673-C52C-4836-BD5F-(E-Mail Removed)...
> > Hi Jessen,
> > Thanks for your prompt reply. If my group contain one row only, then it is
> > fine, but if more than one row, data in cells of column D & E of the
> > second
> > and onward rows will also changed to Total and the formula, how can I fix
> > it
> > please?
> >
> > "Per Jessen" wrote:
> >
> >> Hi
> >>
> >> Maybe something like this. I guess the approach for the formulas has to
> >> be
> >> changed a bit once we know which formulas you need:
> >>
> >> Dim lngRow As Long
> >> For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
> >> If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
> >> Rows(lngRow).EntireRow.Resize(2).Insert
> >> End If
> >> Range("D" & lngRow) = "Total"
> >> Range("E" & lngRow).FormulaR1C1 = "=sum(r[-5]c:r[-1]c)"
> >> Next
> >>
> >> Regards,
> >> Per
> >>
> >> "Seeker" <(E-Mail Removed)> skrev i meddelelsen
> >> news:0CA04C62-F84C-4A05-B660-(E-Mail Removed)...
> >> > Dear volunteers,
> >> > I got following script from the discussion group months ago to separate
> >> > groups of data with two empty rows base on cells in column A. Now I
> >> > would
> >> > like to add text and formula in column D and E (in every first empty
> >> > row)
> >> > at
> >> > bottom of each groups, any ideahow can I do that?
> >> >
> >> > Dim lngRow As Long
> >> > For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
> >> > If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
> >> > Rows(lngRow).EntireRow.Resize(2).Insert
> >> > End If
> >> > Next
> >> >
> >> > Regards
> >>
> >>
>
>

Thanks for your reply, I am glad to help.

/Per

"Seeker" <(E-Mail Removed)> skrev i meddelelsen
news:3F1C2D42-D5B2-4BE8-8579-(E-Mail Removed)...
> Hi Per Jessen,
> Thank you very much, your codes works.
> Regards
>