2

In my active sheet called Report I have 2 column I & F.

It has repeating cells containing text "Grand Total".

I would like to delete whole row if it contains Grand Total automatically.

VBA code would be nice.

enter image description here

brettdj
  • 54,857
  • 16
  • 114
  • 177
Mowgli
  • 3,422
  • 21
  • 64
  • 88
  • 1
    You can do this manually or via code with `AutoFilter`. Coding wise `AutoFilter` is much quicker than looping through ranges – brettdj Nov 30 '12 at 00:29

3 Answers3

3

With the following VBA code, you can quickly delete the rows with certain cell value, please do as the following steps:

  1. Select the range that you want to delete the specific row.

  2. Click Developer>Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:

    VBA code to Remove entire rows based on cell value(i.e. Grand Total):

    Sub Delete_Rows()
    Dim rng As Range, cell As Range, del As Range
    Set rng = Intersect(Range("A1:D22"), ActiveSheet.UsedRange)
    For Each cell In rng
    If (cell.Value) = "Grand Total" _
    Then
    If del Is Nothing Then
    Set del = cell
    Else: Set del = Union(del, cell)
    End If
    End If
    Next cell
    On Error Resume Next
    del.EntireRow.Delete
    End Sub

  3. Then click "Play/Run" button to run the code, and the rows which have certain value have been removed.

(Note: If you can't see Developer Tab in Excel Do these Steps: 1)Click the Office Button, and then click Excel Options. 2)In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.)

Naeem Ul Wahhab
  • 2,465
  • 4
  • 32
  • 59
  • This code runs on `A1:D22` - how is that relevant to the question? – brettdj Nov 30 '12 at 00:31
  • @brettdj Intersect Range Method returns a Range object that represents the Intersection of Ranges. (A1:D22) is just a reference, the code 'Intersect(Range("A1:D22"), ActiveSheet.UsedRange)' will select the intersection of A1:D22 and user selection – Naeem Ul Wahhab Nov 30 '12 at 01:48
  • @TheNoble-Coder YEs, I understand `Intersect`. My point is that the OP example works on column F and I, why use code that runs on the used portion of a different range? And as an aside `ActiveSheet.UsedRange` is not the user selection. – brettdj Nov 30 '12 at 03:24
2

Using AutoFilter is very efficient. This can also be done without VBA (ie manually)

Main Sub

Sub Main()
Dim ws As Worksheet
Dim rng1 As Range
Dim StrIn As String
Dim rng2  As Range
Set ws = Sheets("Sheet1")
Application.ScreenUpdating = False
Set rng1 = ws.Range("F:F,I:I")
StrIn = "Grand Total"
For Each rng2 In rng1.Columns
Call FilterCull(rng2, StrIn)
Next
Application.ScreenUpdating = True
End Sub

Delete Sub

Sub FilterCull(ByVal rng2, ByVal StrIn)
With rng2
    .Parent.AutoFilterMode = False
    .AutoFilter Field:=1, Criteria1:=StrIn
   .EntireRow.Delete
    .Parent.AutoFilterMode = False
    End With
End Sub
brettdj
  • 54,857
  • 16
  • 114
  • 177
1

This should help get you started.

http://msdn.microsoft.com/en-us/library/office/ee814737%28v=office.14%29.aspx#odc_Office14_ta_GettingStartedWithVBAInExcel2010_VBAProgramming101

Also see similar question: Delete a row in Excel VBA

Community
  • 1
  • 1
Michael
  • 1,786
  • 5
  • 23
  • 42