Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete rows with cells less than user-inputed time | Excel Programming | |||
How do I only delete/clear the visible cells in a filtered list? | Excel Worksheet Functions | |||
Help to clear data & delete rows | Excel Programming | |||
Delete/clear a cell based on another cells contents | Excel Programming | |||
Macro to delete rows with text cells | Excel Programming |