We help IT Professionals succeed at work.

Rewriting a Delete Rows Sub

81 Views
Last Modified: 2017-03-30
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

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
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 LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
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.
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Distinguished Expert 2018

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