Macro to Clear Cells and Delete Rows

[mt_pelion]

I am trying to automate the formatting of an Excel output file so that I can
send it to clients. To do this I need a macro that will:

1. Check each Cell in Column J
2. If Cell in Column J is = to "H" I need to clear the Cells in Column J & K
for that Row.
3. If Cell in Column J is = to "T", "R", "D", or "P" I need to delete the
the entire row.

I would appreciate any and all help with this.

Thanks!

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub marine()
Dim myrange, MyRange1 As Range, ClearRange As Range
lastrow = Cells(Rows.Count, "J").End(xlUp).Row
Set myrange = Range("J1:J" & lastrow)
For Each c In myrange
If c.Value = "H" Then
Set ClearRange = c.Resize(, 2)
ClearRange.ClearContents
ElseIf c.Value = "T" Or c.Value = "R" Or c.Value = "D" Or c.Value = "P"
Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub


Mike

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long

With Worksheets("somesheetnamehere")
FirstRow = 1
LastRow = .Cells(.Rows.Count, "J").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
Select Case UCase(.Cells(iRow, "J").Value)
Case Is = "H"
.Cells(iRow, "J").Resize(1, 2).ClearContents
Case Is = "T", "R", "D", "P"
.Rows(iRow).Delete
End Select
Next iRow
End With

End Sub