Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting cells
I have a list of records. Each record goes from col A thru F.
I want to delete the record (not the row) if col A has a X (not case sensitive) in it Oldjay |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting cells
Hi OldJay,
'------------------ I have a list of records. Each record goes from col A thru F. I want to delete the record (not the row) if col A has a X (not case sensitive) in it '------------------ Try something like: '================ Public Sub DeleteRange() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim rCell As Range Dim delRng As Range Dim CalcMode As Long Set WB = Workbooks("MyBook.xls") '<<===== CHANGE Set SH = WB.Sheets("Sheet1") '<<===== CHANGE Set Rng = SH.Range("A1").CurrentRegion.Columns(1) ' On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If StrComp(rCell.Value, "X", vbTextCompare) = 0 Then If delRng Is Nothing Then Set delRng = rCell.Resize(1, 6) Else Set delRng = Union(rCell.Resize(1, 6), delRng) End If End If Next rCell If Not delRng Is Nothing Then delRng.Delete End If XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '<<================ --- Regards, Norman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting cells
Hi Old Jay,
' On Error GoTo XIT should read: On Error GoTo XIT --- Regards, Norman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting cells
try this
for each c in range("a2:a33") if ucase(c)="X" then c.clearcontents next c -- Don Guillett SalesAid Software "Oldjay" wrote in message ... I have a list of records. Each record goes from col A thru F. I want to delete the record (not the row) if col A has a X (not case sensitive) in it Oldjay |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting cells
Hi OldJay,
Prompted by Don's response, if your intention is to delete the data but retain the empty cells, change: delRng.Delete to delRng.ClearContents --- Regards, Norman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting cells
here's one way to do it:
Sub test() Dim rng As Range Dim c As Range Set rng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:F")) For Each c In rng.Columns(1).Cells If UCase(c.Text) = "X" Then rng.Rows(c.Row).ClearContents End If Next c End Sub -- Hope that helps. Vergel Adriano "Oldjay" wrote: I have a list of records. Each record goes from col A thru F. I want to delete the record (not the row) if col A has a X (not case sensitive) in it Oldjay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Cells or Rows of Cells | Excel Discussion (Misc queries) | |||
deleting cells | Excel Programming | |||
deleting unused cells / getting rid of inactive cells | Excel Discussion (Misc queries) | |||
Deleting cells | Excel Programming | |||
Deleting Hyphens or Dashes from multiple cells without deleting the remaining content | Excel Programming |