Deleting cells

[Guest]

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

 

[Norman Jones]

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
'<<================

 

[Norman Jones]

Hi Old Jay,

' On Error GoTo XIT

should read:

On Error GoTo XIT

 

[Don Guillett]

try this
for each c in range("a2:a33")
if ucase(c)="X" then c.clearcontents
next c

 

[Norman Jones]

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

 

[Guest]

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