Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If value is equal, if not a blank row is inserted.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If value is equal, if not a blank row is inserted.
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If value is equal, if not a blank row is inserted.
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If value is equal, if not a blank row is inserted.
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import csv file - blank lines inserted | Excel Discussion (Misc queries) | |||
Why does blank text equal 0? | Excel Worksheet Functions | |||
I don't want blank tp equal zero | Excel Discussion (Misc queries) | |||
Blank space gets inserted after text | New Users to Excel | |||
Formula to equal Blank | Excel Programming |