deleting unneeded rows
 

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!

--
Brian Matlac
-----------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...nfo&userid=350
View this thread: http://www.excelforum.com/showthread.php?threadid=52446

deleting unneeded rows
 

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" wrote in message
news:Brian.Matlack.24zaja_1142875202.2055@excelfor um-nospam.com...

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

deleting unneeded rows
 

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

deleting unneeded rows
 

Ron de Bruin Wrote:
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
wrote i
message
news:Brian.Matlack.24zaja_1142875202.2055@excelfor um-nospam.com...

Hi!
I want to delete all rows that do not contain the numbers 1, 2, 3 o
4
in column A. (some column A cells have letters and some are blan
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 Matlac
-----------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...nfo&userid=350
View this thread: http://www.excelforum.com/showthread.php?threadid=52446

deleting unneeded rows
 

Tom Ogilvy Wrote:
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 cell4 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 o
4
in column A. (some column A cells have letters and some are blan
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!

--
Brian Matlac
-----------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...nfo&userid=350
View this thread: http://www.excelforum.com/showthread.php?threadid=52446