Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows with 0
Hi Folks
Need a way to delete rows if coulmn B D E contain 0 Any help thanks DJ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows with 0
-----Original Message----- Hi Folks, Need a way to delete rows if coulmn B D E contain 0. Any help? thanks, DJ . I did something similiar, my code was like this. Sub DeletRows() Dim myRow As Long Row = 2 Do If myRow 2000 Then Exit Do Select Case Range("N" & myRow).Value Case "S": myRow = myRow + 1 Case Else Rows(myRow & ":" & myRow).Select Selection.Delete Shift:=xlShiftUp End Select Loop End Sub I was simply deleting any rows that didn't have a "S" in column N. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows with 0
Hi
try the following (adapting http://www.cpearson.com/excel/deleti...eleteBlankRows) Public Sub DeleteZeroRows() Dim R As Long Dim C As Range Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If For R = Rng.Rows.Count To 1 Step -1 If cells(r,"B").value = 0 and _ cells(r,"D").value = 0 and _ cells(r,"E").value = 0 Then Rng.Rows(R).EntireRow.Delete End If Next R EndMacro: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub -- Regards Frank Kabel Frankfurt, Germany "Duncan J" schrieb im Newsbeitrag ... Hi Folks, Need a way to delete rows if coulmn B D E contain 0. Any help? thanks, DJ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows with 0
Try this one
< you must have data (or header) in row 1 to use the UsedRange example Sub Example1() Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False For Lrow = .UsedRange.Rows.Count To 1 Step -1 If .Cells(Lrow, "B").Value = 0 And _ .Cells(Lrow, "D").Value = 0 And _ .Cells(Lrow, "E").Value = 0 Then .Rows(Lrow).Delete Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub See for more examples this page http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Duncan J" wrote in message ... Hi Folks, Need a way to delete rows if coulmn B D E contain 0. Any help? thanks, DJ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows with 0
While waiting for my post to appear I came up with this and it works, however, you could boot up a commadore computer faster than this macro will ru
With Range("A1:E50000" .AutoFilte .AutoFilter 2, .AutoFilter 4, .AutoFilter 5, If .Columns(1).SpecialCells(xlVisible).Count 1 The .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Delete Shift:=xlU End I .AutoFilte End With |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows with 0
Sub DeleteRows()
Dim rng As Range Set rng = Range("B:B,D:E") rng.Replace What:=0, Replacement:="=NA()" On Error Resume Next Set rng1 = rng.SpecialCells(xlCellTypeFormulas, _ xlErrors) On Error GoTo 0 If Not rng1 Is Nothing Then Set rng1 = Intersect(rng1.EntireRow, Columns(1)) rng1.EntireRow.Delete End If End Sub -- Regards, Tom Ogilvy "Duncan J" wrote in message ... Hi Folks, Need a way to delete rows if coulmn B D E contain 0. Any help? thanks, DJ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows with 0
All three columns in the same row have to contain zero?
-- Regards, Tom Ogilvy "Duncan J" wrote in message ... While waiting for my post to appear I came up with this and it works, however, you could boot up a commadore computer faster than this macro will run With Range("A1:E50000") .AutoFilter .AutoFilter 2, 0 .AutoFilter 4, 0 .AutoFilter 5, 0 If .Columns(1).SpecialCells(xlVisible).Count 1 Then .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Delete _ Shift:=xlUp End If .AutoFilter End With |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows with 0
Note, this does not demand that zeros appear in all three columns for each
row that is to be deleted - so I believe it does not fit your requirement. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub DeleteRows() Dim rng As Range Set rng = Range("B:B,D:E") rng.Replace What:=0, Replacement:="=NA()" On Error Resume Next Set rng1 = rng.SpecialCells(xlCellTypeFormulas, _ xlErrors) On Error GoTo 0 If Not rng1 Is Nothing Then Set rng1 = Intersect(rng1.EntireRow, Columns(1)) rng1.EntireRow.Delete End If End Sub -- Regards, Tom Ogilvy "Duncan J" wrote in message ... Hi Folks, Need a way to delete rows if coulmn B D E contain 0. Any help? thanks, DJ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows with 0
Hi Tom, for some reason your macro deleted the entire spreadsheet.
Thanks.. Frank, however, yours took a little longer than mine to run. R. Mitchel thanks. still looking at your code. Ron your code worked as well, however, they all seem to take about 5 minutes to run... I'll reboot my machine and try each one again. At this point I think I'll use my code. Thanks again... Very nice info. DJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
delete empty rows between rows with text | Excel Discussion (Misc queries) |