Rewriting a Delete Rows Sub

RWayneH
RWayneH used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
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

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:
Well the Sheet name is really not Sheet2, it chg's need it to be whatever the active sheet is.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Then change

Set Ws = ActiveSheet
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Change
Set Ws = Worksheets("Sheet2")

to

Set Ws = Activesheet

Author

Commented:
Thanks for the help.
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
You're Welcome RwayneH! Glad I was able to help :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial