Remove Data from rows

[RJ1969]

Hello,

Can you please create me a macro that will Delete the data that has ?YES? in column H4:H3000 and it will leave no empty rows from A4:H3000 (Sheet1)

See attachment.

Thank you in advance!

[rollis13]

This could be a macro for you, have a try on a test file.

Option Explicit
Sub DeleteItems()
    With Sheets("Sheet1").Range("$B3:$H$3000")         '<- use this is for a static range
    'With Sheets("Sheet1").Range("$B3:$H$" & Cells(Rows.Count, "B").End(xlUp).Row) '<- use this instead for a dynamic range
        Sheets("Sheet1").AutoFilterMode = False
        .AutoFilter Field:=7, Criteria1:="YES", Operator:=xlFilterValues
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        Sheets("Sheet1").AutoFilterMode = False
    End With
End Sub

[RJ1969]

Hello,

It worked! Thank you.

Will be able also to make me a macro for Duplicate Entry in Column B4:B3000?
Delete the duplicate and leave no empty rows as well.

I want it a separate button, if you can. Thank you so much

[rollis13]

Sorry, this is a completely different request. I suggest opening a new thread with an appropriate title.
As per 'removing data', glad having been of some help.

[RJ1969]

Ok will do. thank you so much

[RJ1969]

Can you add a message box? " Are you sure you want to delete" with Ok and Cancel.

Thank you

[rollis13]

Add these three lines of code to your macro.

Option Explicit
Sub DeleteItems()
    Dim answer As String
    answer = MsgBox("Are you sure you want to delete?", vbYesNo)
    If answer = vbNo Then Exit Sub
    With Sheets("Sheet1").Range("$B3:$H$3000")    '<- use this is for a static range
        'With Sheets("Sheet1").Range("$B3:$H$" & Cells(Rows.Count, "B").End(xlUp).Row) '<- use this instead for a dynamic range
        Sheets("Sheet1").AutoFilterMode = False
        .AutoFilter Field:=7, Criteria1:="YES", Operator:=xlFilterValues
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        Sheets("Sheet1").AutoFilterMode = False
    End With
End Sub

[RJ1969]

Hi rollis13

Ok I did change the code.
But Why it deletes the filter after I click the macro button? Can you fix it please. (See attachment)

Thank you

[RJ1969]

Hi

Alao please
maybe instead using Sheet1 if you can change it to activesheet so i can use the same maco button for Sheet2?

I tried to use the code for sheet2 but it did not work.

Thank you so much

[beyond Excel]

.
Hi. Try with:

Sub DeleteItems()
If MsgBox("Are you sure you want to delete?", vbYesNo) = vbNo Then Exit Sub
With Range("$H3", Cells(Rows.Count, "B").End(xlUp))
  .AutoFilter
  .AutoFilter Field:=7, Criteria1:="YES", Operator:=xlFilterValues
  If WorksheetFunction.Subtotal(3, .Columns(7)) > 1 Then .Offset(1).SpecialCells(12).EntireRow.Delete
  .Parent.ShowAllData
End With
End Sub

[RJ1969]

Hi BeyondExcel,

I have one issue. Please fix.

I may need to hide a column or columns. Will you be able to fix so that I can hide some columns in the worksheet.

The sample had hidden column and an error said its overlapping.

Thank you so much.

[beyond Excel]

Hello. I saw that you have coded a sheet event.
So try it this way:

Sub DeleteItems() 
If MsgBox("Are you sure you want to delete?", vbYesNo) = vbNo Then Exit Sub 
With Range("$H3", Cells(Rows.Count, "B").End(xlUp)) 
  .AutoFilter 
  .AutoFilter Field:=7, Criteria1:="YES", Operator:=xlFilterValues 
  If WorksheetFunction.Subtotal(3, .Columns(7)) > 1 Then 
    Application.EnableEvents = False 
      .Offset(1).EntireRow.Delete 
    Application.EnableEvents = True 
  End If 
  .Parent.ShowAllData 
End With 
End Sub