Hi!
I want to delete all rows that do not contain the numbers 1, 2, 3 or
in column A. (some column A cells have letters and some are blank thes
are the ones I want to delete) How can I do this with a macro?
Thanks!
Ron de Bruin - 20 Mar 2006 18:59 GMT
You can try this Brian
Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf .Cells(Lrow, "A").Value <> "1" And _
.Cells(Lrow, "A").Value <> "2" And _
.Cells(Lrow, "A").Value <> "3" And _
.Cells(Lrow, "A").Value <> "4" Then .Rows(Lrow).Delete
End If
Next
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Signature
Regards Ron de Bruin
http://www.rondebruin.nl
> Hi!
> I want to delete all rows that do not contain the numbers 1, 2, 3 or 4
> in column A. (some column A cells have letters and some are blank these
> are the ones I want to delete) How can I do this with a macro?
> Thanks!!
Brian Matlack - 20 Mar 2006 20:26 GMT
Ron de Bruin Wrote:
> You can try this Brian
>
[quoted text clipped - 66 lines]
> > View this thread
> http://www.excelforum.com/showthread.php?threadid=524463
Thanks Ron this works GREAT!
Tom Ogilvy - 20 Mar 2006 19:03 GMT
Dim rng as Range, i as Long, lastrow as Long
On error resume next
set rng = Columns(1).Specialcells(xlblanks)
rng.EntireRow.Delete
set rng = columns(1).Specialcells(xlConstants,xlTextValues)
rng.Entirerow.Delete
On error go 0
lastrow = cells(rows.count,1).End(xlup).row
for i = lastrow to 1 step -1
set cell = cells(i,1)
if isnumeric(cell) then
if cell>4 or cell < 1 then
rows(i).Delete
end if
else
rows(i).delete
end if
Next
Untested pseudocode.

Signature
Regards,
Tom Ogilvy
> Hi!
> I want to delete all rows that do not contain the numbers 1, 2, 3 or 4
> in column A. (some column A cells have letters and some are blank these
> are the ones I want to delete) How can I do this with a macro?
> Thanks!!
Brian Matlack - 20 Mar 2006 20:27 GMT
Tom Ogilvy Wrote:
> Dim rng as Range, i as Long, lastrow as Long
> On error resume next
[quoted text clipped - 36 lines]
> > View this thread
> http://www.excelforum.com/showthread.php?threadid=524463
Thanks Tom this one works Great also!