Macro to Clear Cells and Delete Rows
 

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!

Macro to Clear Cells and Delete Rows
 

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

"mt_pelion" wrote:

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!

Macro to Clear Cells and Delete Rows
 

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


mt_pelion wrote:

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!

--

Dave Peterson