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
436 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 33

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

820 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