deleting unneeded rows

[Brian Matlack]

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!!

[Ron de Bruin]

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brian Matlack" <[email protected]> wrote in message
news:[email protected]...
>
> 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
> ------------------------------------------------------------------------
> Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
> View this thread: http://www.excelforum.com/showthread...hreadid=524463
>

[Tom Ogilvy]

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.
--
Regards,
Tom Ogilvy



"Brian Matlack" wrote:

>
> 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
> ------------------------------------------------------------------------
> Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
> View this thread: http://www.excelforum.com/showthread...hreadid=524463
>
>

[Brian Matlack]

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

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Brian Matlack" <[email protected]> wrote in message
news:[email protected]...
>
> 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
> ------------------------------------------------------------------------
> Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
> View this thread: http://www.excelforum.com/showthread...hreadid=524463
>

Thanks Ron this works GREAT!!

[Brian Matlack]

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.
--
Regards,
Tom Ogilvy



"Brian Matlack" wrote:

>
> 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
> ------------------------------------------------------------------------
> Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
> View this thread: http://www.excelforum.com/showthread...hreadid=524463
>
>

Thanks Tom this one works Great also!!