If value is equal, if not a blank row is inserted.

[Emajination]

I need to have a formula that checks each row in a spreadsheet and
compares the values in columns B, D, E. If the values in the all three
columns are the same, no action is required. If the value in all three
columns changes, a blank row should be inserted between the lines that
differ. So it sounds like i need a formula to do the comparison that
will call in a macro to do the row insertion when it applies.

Thank you in advance

[Tom Ogilvy]

A formula can't call a macro that will do that. You would use the Calculate
event
http://www.cpearson.com/excel/events.htm for an overview

Right click on the sheet tab

Select view code

In the left dropdown at the top of the resulting module select worksheet and
from the left dropdown select Calculate

Private Sub Worksheet_Calculate()
Dim lastrow as Long
set lastrow = cells(rows.count,2).end(xlup).row
for i = lastrow to 2 Step -1
if application.countA(cells(i,"B"),cells(i,"D"),cells(i,"E")) <> 0 and _
application.CountA(cells(i-1,"B"),cells(i-1,"D"),cells(i-1,"E")) <> 0 then
if cells(i,"B") <> cells(i-1,"B") or cells(i,"D") <> cells(i-1,"D") _
or cells(i,"E") <> cells(i-1,"E") then
rows(i).EntireRow.Insert
End if
end if
Next
End sub

--
Regards,
Tom Ogilvy


"Emajination" wrote:

> I need to have a formula that checks each row in a spreadsheet and
> compares the values in columns B, D, E. If the values in the all three
> columns are the same, no action is required. If the value in all three
> columns changes, a blank row should be inserted between the lines that
> differ. So it sounds like i need a formula to do the comparison that
> will call in a macro to do the row insertion when it applies.
>
> Thank you in advance
>
>

[Emajination]

Thank you Tom for the quick response

That was very helpful. Please forgive my lack of knowledge, but I'm
unsure how to get the Macro to work. This is actually my first time
creating a macro. I've pasted the code into the VBA, but I'm sure if I
should save the file, or click Run (F5).

Any assistance would be helpful.

[Tom Ogilvy]

If you placed it as I described, then
the macro should run everytime the sheet is recalculated.

This is consistent with your concept of using a formula to do the work.

I did note I had a typo in the code. This is the tested revision:

Private Sub Worksheet_Calculate()
Dim lastrow As Long
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
For i = lastrow To 2 Step -1
If Application.CountA(Cells(i, "B"), Cells(i, "D"), Cells(i, "E")) <> 0
And _
Application.CountA(Cells(i - 1, "B"), Cells(i - 1, "D"), Cells(i - 1,
"E")) <> 0 Then
If Cells(i, "B") <> Cells(i - 1, "B") Or Cells(i, "D") <> Cells(i - 1,
"D") _
Or Cells(i, "E") <> Cells(i - 1, "E") Then
Rows(i).EntireRow.Insert
End If
End If
Next
End Sub

It worked for me when I hit F9 to force a calculate.

However, I am not sure what you are doing or how you will be changing your
worksheet. If this is a one time thing, then you don't want to use the
calculate event. You would go into the VBE (alt + F11) and choose Insert =>
Module from the menu. In the module you would put this code:

Public Sub AddRows()
Dim lastrow As Long
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
For i = lastrow To 2 Step -1
If Application.CountA(Cells(i, "B"), Cells(i, "D"), Cells(i, "E")) <> 0
And _
Application.CountA(Cells(i - 1, "B"), Cells(i - 1, "D"), Cells(i - 1,
"E")) <> 0 Then
If Cells(i, "B") <> Cells(i - 1, "B") Or Cells(i, "D") <> Cells(i - 1,
"D") _
Or Cells(i, "E") <> Cells(i - 1, "E") Then
Rows(i).EntireRow.Insert
End If
End If
Next
End Sub

the go back to excel and if you wanted to run it you would go

Tools=>Macro=>Macros, select Addrows and hit the run button.

or stay in the VBE, click in the macro and hit F5 as you describe.

Whatever you do, like any other change to a workbook, you need to save the
workbook to save the macro.

--
Regards,
Tom Ogilvy


"Emajination" wrote:

> Thank you Tom for the quick response
>
> That was very helpful. Please forgive my lack of knowledge, but I'm
> unsure how to get the Macro to work. This is actually my first time
> creating a macro. I've pasted the code into the VBA, but I'm sure if I
> should save the file, or click Run (F5).
>
> Any assistance would be helpful.
>
>