How to delete rows based on cell content
Category: [Excel] Demo Available
How to delete rows based on cell content
Requirement:
The goal: every instance of a cell containing the word "Page" in column C will cause excel to move up 1 row, then delete the next 9 rows.
Example:
Cell C20 has "Page: 0006"
Excel recognizes this, then selects row 19:27 and deletes them.
Loops back around until its at the bottom of the dataset and stops
The user has several code snippets here and there that would do some of these functions, but the user is not strong in VBA and doesn't get how to set variables, utilize offsets, and a few other elements needed here. The dataset is very irregular....but this 9 row rule seems to be how the user differentiates a new page header.
Solution:
Code:
Sub DeleteRows()
Dim i As Long
Application.ScreenUpdating = 0
With Sheets("Paste AP Report")
For i = .UsedRange.Rows.Count To 15 Step -1
If .Cells(i, 3) Like "Page*" Then
.Rows(i - 1).Resize(9).EntireRow.Delete
End If
Next
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to Delete/Hide every nth row |
| How to disable edit/delete comments |
| How to create, apply or delete a custom view |
| How to delete row if Date/Time between 05:00 - 20:00 |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.