Delete row if a cell text equals the cell below

S

srschicago

Board Regular
Joined
Apr 14, 2017
Messages
59
I create a report worksheet that varies in length. The report includes "*Phase" title rows that may or may not include data rows before the next title row.
I want to delete the title rows that do not have data rows following the title row.
The cell in column C of each title row has the text "*Phase".
How do I write the code to delete the row if the text "*Phase" in cell C of the row equals the text "*Phase" in cell C of the row immediately following?
The report worksheet has a header row that does not list *Phase in the header for column C.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
I struggled with the attachment options, but it looks like the xlam addin is installed in my Excel 2010 version
I hope this snapshot of the worksheet comes across. The column C header row text is "Responsible"
ItemItem DescriptionResponsible Due DateCompleted Date
DPP 564 CPU-4520 Change PRELIMINARY PHASE*Phase
DPP 564 CPU-4520 Change PROTOTYPE PHASE*Phase
DPP 564 CPU-4520 Change PILOT PHASE*Phase
Complete new component cost fields in the MIS Part MaintenanceB.Riely02/15/17
Purchase 050-0212-003AB.Riely02/15/17
Purchase 110-0216-003A B701B.Riely02/15/17
Purchase 300-2270-452RB.Riely02/15/17
Purchase 520-0102-SPB.Riely02/15/17
Purchase 520-0107-PB.Riely02/15/17
DPP 564 CPU-4520 Change MANUFACTURING PHASE*Phase
DPP 565 Chassis 2450 Change PRELIMINARY PHASE*Phase
DPP 565 Chassis 2450 Change PROTOTYPE PHASE*Phase

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
srschicago,

Thanks for the raw data flat text display.

1. What is the worksheet name?

2. Can we have another flat text display of what the results should look like?
 
Upvote 0
srschicago said:
I struggled with the attachment options, but it looks like the xlam addin is installed in my Excel 2010 version
I hope this snapshot of the worksheet comes across. The column C header row text is "Responsible"
ItemItem DescriptionResponsible Due DateCompleted Date
DPP 564 CPU-4520 Change PRELIMINARY PHASE*Phase
DPP 564 CPU-4520 Change PROTOTYPE PHASE*Phase
DPP 564 CPU-4520 Change PILOT PHASE*Phase
Complete new component cost fields in the MIS Part MaintenanceB.Riely02/15/17
Purchase 050-0212-003AB.Riely02/15/17
Purchase 110-0216-003A B701B.Riely02/15/17
Purchase 300-2270-452RB.Riely02/15/17
Purchase 520-0102-SPB.Riely02/15/17
Purchase 520-0107-PB.Riely02/15/17
DPP 564 CPU-4520 Change MANUFACTURING PHASE*Phase
DPP 565 Chassis 2450 Change PRELIMINARY PHASE*Phase
DPP 565 Chassis 2450 Change PROTOTYPE PHASE*Phase

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
Click to expand...

What is the desired result?
 
Upvote 0
Thanks to both respondents. I wand to change the worksheet "REPORTS" from -
ItemItem DescriptionResponsible Due DateCompleted Date
DPP 564 CPU 5420 change PRELIMINARY PHASE*Phase
DPP 564 CPU 5420 change PROTOTYPE PHASE*Phase
DPP 564 CPU 5420 change PILOT PHASE*Phase
Complete new component cost fields in the MIS Part MaintenanceB.Riely02/15/17
Purchase 520-0102-SPB.Riely02/15/17
Purchase 520-0107-PB.Riely02/15/17
DPP 564 CPU 5420 change MANUFACTURING PHASE*Phase
DPP 565 2450 Chassis Change PRELIMINARY PHASE*Phase
DPP 565 2450 Chassis Change PROTOTYPE PHASE*Phase
DPP 564 CPU 5420 change PROTOTYPE PHASE*Phase
DPP 564 CPU 5420 change PILOT PHASE*Phase
Complete/verify new component planning fields in the MIS Part MaintenanceC.Pearl02/15/17
Run W/O Material Availailability Report - Verify all components are availableC.Pearl02/15/17
Create 900-0216-010A Pilot work ordersC.Pearl02/15/17
Create 956-2212-003A Pilot work ordersC.Pearl02/15/17
Create 954-2212-003A Pilot work ordersC.Pearl02/15/17
DPP 564 CPU 5420 change MANUFACTURING PHASE*Phase
DPP 565 2450 Chassis Change PRELIMINARY PHASE*Phase
DPP 565 2450 Chassis Change PROTOTYPE PHASE*Phase
DPP 565 2450 Chassis Change iPack PILOT PHASE*Phase
Run W/O Material Availailability Report - Verify all components are availableC.Pearl02/15/17
Verify all components are purchased per required datesC.Pearl02/15/17
DPP 565 2450 Chassis Change iPack MANUFACTURING PHASE*Phase



<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
change to -
ItemItem DescriptionResponsible Due DateCompleted Date
DPP 564 CPU 5420 change PRELIMINARY PHASE*Phase
Complete new component cost fields in the MIS Part MaintenanceB.Riely02/15/17
Purchase 520-0102-SPB.Riely02/15/17
Purchase 520-0107-PB.Riely02/15/17
DPP 564 CPU 5420 change PILOT PHASE*Phase
Complete/verify new component planning fields in the MIS Part MaintenanceC.Pearl02/15/17
Run W/O Material Availailability Report - Verify all components are availableC.Pearl02/15/17
Create 900-0216-010A Pilot work ordersC.Pearl02/15/17
Create 956-2212-003A Pilot work ordersC.Pearl02/15/17
Create 954-2212-003A Pilot work ordersC.Pearl02/15/17
DPP 565 2450 Chassis Change iPack PILOT PHASE*Phase
Run W/O Material Availailability Report - Verify all components are availableC.Pearl02/15/17
Verify all components are purchased per required datesC.Pearl02/15/17

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Why did you keep the first row for the first group of *Phases, the last row for the next two groups and you deleted the case where there was only one *Phase... please explain your logic for us.
 
Upvote 0
I sincerely apologize. I deleted the wrong two rows in the example. I should have deleted row two and three instead of three and four.
I only want to report *Phases where an item is due. If a phase row does not have items due in rows immediately following it, that phase row should be deleted.
 
Upvote 0
It'd be easier for me to understand which rows to delete if you could highlight them in red color. That way it'd be instantly clear which rows to delete and there'd be need to compare "before and after" trying to figure out which rows are deleted.
 
Last edited:
Upvote 0
srschicago said:
I sincerely apologize. I deleted the wrong two rows in the example. I should have deleted row two and three instead of three and four.
I only want to report *Phases where an item is due. If a phase row does not have items due in rows immediately following it, that phase row should be deleted.
Click to expand...

srschicago,

If I understand you correctly, then here is a macro solution for you to consider.

Sample raw data:


Excel 2007
ABCDE
1ItemItem DescriptionResponsibleDue DateCompleted Date
2DPP 564 CPU 5420 change PRELIMINARY PHASE*Phase
3DPP 564 CPU 5420 change PROTOTYPE PHASE*Phase
4DPP 564 CPU 5420 change PILOT PHASE*Phase
5Complete new component cost fields in the MIS Part MaintenanceB.Riely2/15/2017
6Purchase 520-0102-SPB.Riely2/15/2017
7Purchase 520-0107-PB.Riely2/15/2017
8DPP 564 CPU 5420 change MANUFACTURING PHASE*Phase
9DPP 565 2450 Chassis Change PRELIMINARY PHASE*Phase
10DPP 565 2450 Chassis Change PROTOTYPE PHASE*Phase
11DPP 564 CPU 5420 change PROTOTYPE PHASE*Phase
12DPP 564 CPU 5420 change PILOT PHASE*Phase
13Complete/verify new component planning fields in the MIS Part MaintenanceC.Pearl2/15/2017
14Run W/O Material Availailability Report - Verify all components are availableC.Pearl2/15/2017
15Create 900-0216-010A Pilot work ordersC.Pearl2/15/2017
16Create 956-2212-003A Pilot work ordersC.Pearl2/15/2017
17Create 954-2212-003A Pilot work ordersC.Pearl2/15/2017
18DPP 564 CPU 5420 change MANUFACTURING PHASE*Phase
19DPP 565 2450 Chassis Change PRELIMINARY PHASE*Phase
20DPP 565 2450 Chassis Change PROTOTYPE PHASE*Phase
21DPP 565 2450 Chassis Change iPack PILOT PHASE*Phase
22Run W/O Material Availailability Report - Verify all components are availableC.Pearl2/15/2017
23Verify all components are purchased per required datesC.Pearl2/15/2017
24DPP 565 2450 Chassis Change iPack MANUFACTURING PHASE*Phase
25
REPORTS


And, after the macro:


Excel 2007
ABCDE
1ItemItem DescriptionResponsibleDue DateCompleted Date
2DPP 564 CPU 5420 change PILOT PHASE*Phase
3Complete new component cost fields in the MIS Part MaintenanceB.Riely2/15/2017
4Purchase 520-0102-SPB.Riely2/15/2017
5Purchase 520-0107-PB.Riely2/15/2017
6DPP 564 CPU 5420 change PILOT PHASE*Phase
7Complete/verify new component planning fields in the MIS Part MaintenanceC.Pearl2/15/2017
8Run W/O Material Availailability Report - Verify all components are availableC.Pearl2/15/2017
9Create 900-0216-010A Pilot work ordersC.Pearl2/15/2017
10Create 956-2212-003A Pilot work ordersC.Pearl2/15/2017
11Create 954-2212-003A Pilot work ordersC.Pearl2/15/2017
12DPP 565 2450 Chassis Change iPack PILOT PHASE*Phase
13Run W/O Material Availailability Report - Verify all components are availableC.Pearl2/15/2017
14Verify all components are purchased per required datesC.Pearl2/15/2017
15DPP 565 2450 Chassis Change iPack MANUFACTURING PHASE*Phase
16
17
18
19
20
21
22
23
24
25
REPORTS


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Sub srschicago()
' hiker95, 05/01/2017, ME1003003
Dim lr As Long, r As Range
Application.ScreenUpdating = False
With Sheets("REPORTS")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For Each r In .Range("C2:C" & lr)
    If r = "*Phase" And r.Offset(1) = "*Phase" Then
      r.ClearContents
    End If
  Next r
  On Error Resume Next
  .Range("C2:C" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the srschicago macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Pivot VBA issue.
Replies
0
Views
293
siddo
S
L
  • Question
Return text from cell in column containing text, as long as values in other columns match several criteria
Replies
7
Views
349
etaf
etaf
CJG19
  • Solved
Dynamic Table Hide Rows
Replies
2
Views
122
CJG19
CJG19
L
  • Question
Find text within a cell using formula
Replies
9
Views
377
Fluff
Fluff
B
  • Solved
Find column and delete rows below....
2
Replies
11
Views
544
Alex Blakenburg
A

Forum statistics

Threads
1,214,599
Messages
6,120,447
Members
448,966
Latest member
DannyC96

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top