Macro to delete all rows with the word "Total"

RandWald · #1 11-16-2016, 05:27 AM

Hi. Ofc365. Win 10. Looking for a macro to delete all rows with the word "Total" contained in text of any row in col A. I am doing a financial export from QuickBooks and I do not want my totals.

thanks, Randy

Pecoflyer · #2 11-16-2016, 10:23 AM

Hi
this kind of problem has been treated many times. Perhaps search the web first ?

Old · #3 11-23-2016, 11:51 PM

There are plenty of tricks you could do to get fixed. Here is one:

Go to your home tab
Go to conditional formatting
Highlight text that contains (enter the word you want)
Filter and sort
Custom Sort
Sort on cell color

Save your macro

Then all you have to do is delete the rows manually each time.

rajeshn_in · #4 11-28-2016, 07:46 PM

Code:

Sub Loop_Example()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("Sheet1")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

        If Application.CountIf(.Rows(Lrow), "Total") > 0 Then .Rows(Lrow).Delete = True
        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

Previously I use this to hide row, try it ....

rajeshn_in · #5 11-28-2016, 07:50 PM

Small correction

change below line

Code:

If Application.CountIf(.Rows(Lrow), "Total") > 0 Then .Rows(Lrow).Delete = True

as

Code:

If Application.CountIf(.Rows(Lrow), "Total") > 0 Then .Rows(Lrow).EntireRow.Delete

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I delete the names under "Exceptions" when I use the "Restrict Editing" feature in Word?	MengS	Word	0	02-25-2015 02:57 PM
remove repeated words with " macro " or " wild cards " in texts with parentheses and commas	jocke321	Word VBA	2	12-10-2014 11:27 AM
Word Macro to find and delete rows that contain adjacent cells containing "."	AlexanderJohnWilley	Word VBA	7	11-08-2012 10:15 AM
Automated "Macro" to delete Tags/Anchors	field3	Word VBA	0	02-25-2009 02:53 PM
word 2002 adding "delete" to drop down	2402RUNE	Word	1	12-07-2008 09:29 AM