Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing unneeded information | New Users to Excel | |||
Deleting unneeded rows and columns | Excel Discussion (Misc queries) | |||
password popup unneeded | Excel Programming | |||
Deleting unneeded characters in a cell | Excel Programming | |||
Deleting Unneeded Records | Excel Programming |