Solved

# Excel VBA - I would like VBA to detect the word "subtotal" in a row, delete that row, move down one row. repeat.

Posted on 2014-10-24
454 Views
I would like VBA to:
1.  Detect the word "subtotal" in a row,
2.  Delete that row,
3.  Move down one row.
4.  Go to step 1.
0
Question by:brothertruffle880
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 15

Accepted Solution

Haris Djulic earned 250 total points
ID: 40402767
Here is the code :

``````i = 1
j = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Do While i < j

If ActiveSheet.Range("A" & i).Value = "subtotal" Then
ActiveSheet.Rows(i).Delete
i = i - 1
j = j - 1
Else
i = i + 1
End If

Loop
``````
0

LVL 27

Assisted Solution

Glenn Ray earned 250 total points
ID: 40402848
This code will check all cells in the active sheet to see if "subtotal" exists in any row and remove that row if so:
``````Sub Delete_Subtotal_Rows()
'based on code from dlmille, Experts Exchange Q_27636554
Dim rng As Range
Dim r As Range
Dim chkRow As String
Dim rng2 As Range
Dim r2 As Range
Dim rDelete As Range
Dim lngLR, lngLC As Long

lngLR = Cells.SpecialCells(xlLastCell).Row
lngLC = Cells.SpecialCells(xlLastCell).Column

Set rng = Range("A2", Cells.SpecialCells(xlLastCell))

For Each r In rng
Set rng2 = Range(Cells(r.Row, 1), Cells(r.Row, lngLC))
For Each r2 In rng2
chkRow = chkRow & Trim(r2.Value)
Next r2
If InStr(1, chkRow, "subtotal") > 0 Then
If rDelete Is Nothing Then
Set rDelete = r
Else
Set rDelete = Union(r, rDelete)
End If
End If
chkRow = vbNullString
Next r
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub
``````

Regards,
-Glenn

PS Haris' code only checks cells in column A.
0

LVL 33

Expert Comment

ID: 40406209
Were the Subtotals created using the Subtotal Wizard?

If so, the Subtotal Wizard also has an option for removing Subtotals. This will remove the rows as required.

Thanks
Rob H
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month9 days, 8 hours left to enroll