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
3
408 Views
Last Modified: 2014-11-13
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
Comment
Question by:brothertruffle880
3 Comments
 
LVL 15

Accepted Solution

by:
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

Open in new window

0
 
LVL 27

Assisted Solution

by:Glenn Ray
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

Open in new window


Regards,
-Glenn

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

Expert Comment

by:Rob Henson
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now