macro with auto filter code requrires ammending

[mingandmong]

HI I'm using excel 2016

the following macro deletes all the rows of data when the word "SHOP" is found in column c

the issue I have is if the word "SHOP" is not found than all the data is removed except just one row

how can I change this

Sub Macro2()
Application.ScreenUpdating = False
With Range("C1", Range("C" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:=Array("SHOP"), Operator:=xlFilterValues
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[kpasa]

Re: macro with auto filtre code requrires ammending

With Range("C1", Range("C" & Rows.Count).End(xlUp))

Because you've specified the range to delete as the entire range. You need to modify your code to loop through row by row of the range, not just the entire range itself.

 

[kpasa]

Re: macro with auto filtre code requrires ammending

Quick and dirty code:

Dim c as range
For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    c.activate
    If c.offset(0,2) = "SHOP" Then
        'add your code to delete the active row
    Else
        'do nothing,
    End If
Next  c

 

[kpasa]

Re: macro with auto filtre code requrires ammending

And your code for deleting a row is this:

[COLOR=#000000][FONT=Consolas]Rows(10).Delete Shift:=xlShiftUp[/FONT][/COLOR]

So all you have to do is get the current row number like so:

[COLOR=#333333]ActiveCell.Row[/COLOR]

To make the code faster, remove the c.activate to keep the gui from having to change:

Dim c as range
For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    'c.activate
    If c.offset(0,2) = "SHOP" Then
        'add your code to delete the active row
	Rows(c.row).Delete Shift:=xlShiftUp
    Else
        'do nothing,
    End If
Next  c

 

[My Aswer Is This]

Re: macro with auto filtre code requrires ammending

Try this:

Sub Macro2()
'Modified  2/4/2019  1:05:28 PM  EST
Application.ScreenUpdating = False
Dim Counter As Long
With Range("C1", Range("C" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, Criteria1:=Array("SHOP"), Operator:=xlFilterValues
    Counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
        If Counter > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Re: macro with auto filtre code requrires ammending

How about

Sub Macro2()
Application.ScreenUpdating = False
With Range("C1", Range("C" & Rows.Count).End(xlUp))
   .AutoFilter Field:=1, Criteria1:=Array("SHOP"), Operator:=xlFilterValues
   On Error Resume Next
   .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).EntireRow.Delete
   On Error GoTo 0
   .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[mingandmong]

Re: macro with auto filtre code requrires ammending

Kpasa

Thank you for post it works

 

[mingandmong]

Re: macro with auto filtre code requrires ammending

Hi
My Answer Is This thank you for taking your time
Kpasa formula works, I but I can see that your and Fluff are different

 

[My Aswer Is This]

Re: macro with auto filtre code requrires ammending

Glad we were able to help you.
Using Excel there are 20 ways to do everything
Filtering in some cases is faster then loops but it all depends on how many rows.
The different's may only be a second or two
Come back here to Mr. Excel next time you need additional assistance.

Hi
My Answer Is This thank you for taking your time
Kpasa formula works, I but I can see that your and Fluff are different