Finding the datas and deleting datas which are not found.
Hi Ddiicc,
One way:
'========================
Public Sub Tester()
Dim rng As Range
Dim rCell As Range
Dim delRng As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim CalcMode As Long
Set WB = ActiveWorkbook '<<========== CHANGE
Set SH = WB.Sheets("Sheet1") '<<========== CHANGE
Set rng = Intersect(SH.UsedRange, Columns("AZ:AZ"))
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
For Each rCell In rng.Cells
If rCell.Value < 5 Then
If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
End If
Next rCell
If Not delRng Is Nothing Then
delRng.EntireRow.Delete
Else
'nothing found, do nothing
End If
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<========================
Alternatively, look at using the autofilter feature.
---
Regards,
Norman
"ddiicc" wrote in message
...
Hi,
I have a column (AZ:AZ) header name "DAY", this column consist of 1 to 31
which the number stand for the day of the month. Let say, i would like to
day number 5 and delete the rest of the rows which does not contain the
day
number 5,
how do I go abt i writing the code for this?? Possible??
Thanks
Tony
|