Rewriting a Delete Rows Sub

How would I adj this code to delete everything that is in column H, that is not an "M"? However I need to do this on the activate sheet tab.

Sub Delete_Data()
    Set stInp = Sheets("Sheet2")
    oriInputCnt = stInp.Cells(stInp.Rows.Count, "H").End(xlUp).Row
    If oriInputCnt > 5 Then stInp.Rows("6:" & oriInputCnt).EntireRow.Delete Shift:=xlUp
End Sub

Open in new window

RWayneHAsked: 2017-03-30

Who is Participating?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license! Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 31

ShumsExcel & VBA ExpertCommented: 2017-03-30

Hi Rwayneh,

Please try below:

Sub Deletes()
Dim Ws As Worksheet
Dim LR As Long, i As Long
Dim v As Variant

Set Ws = Worksheets("Sheet2")
LR = Ws.Range("H" & Rows.Count).End(xlUp).Row

For i = LR To 6 Step -1
    v = Ws.Cells(i, "H").Value
    With Ws.Cells(i, "H")
        If v <> "M" Then
            .EntireRow.Delete
        End If
    End With
Next i
      
End Sub

Open in new window

LVL 58

Martin LissOlder than dirtCommented: 2017-03-30

For no points you don't need the v variable.

Sub Deletes()
Dim Ws As Worksheet
Dim LR As Long, i As Long

Set Ws = Worksheets("Sheet2")
LR = Ws.Range("H" & Rows.Count).End(xlUp).Row

For i = LR To 6 Step -1
    With Ws.Cells(i, "H")
        If .Value <> "M" Then
            .EntireRow.Delete
        End If
    End With
Next i
      
End Sub

Open in new window

RWayneHAuthor Commented: 2017-03-30

Well the Sheet name is really not Sheet2, it chg's need it to be whatever the active sheet is.

LVL 31

ShumsExcel & VBA ExpertCommented: 2017-03-30

Then change

Set Ws = ActiveSheet

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 58

Martin LissOlder than dirtCommented: 2017-03-30

Change
Set Ws = Worksheets("Sheet2")

to

Set Ws = Activesheet