Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row when zero
Hi
Can some one help me with macro or vba code to delete the row when column B,,C,D, and E all 4 have the value of 0 (It has to be 0 in all 4 column). Its very big sheet with thousands of rows. Please help Thank Andy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row when zero
Hi Sorry All, Forget to tell you I dont want this code to look row 1 thru 6. Should start from row 7 and end at row till the data ends
Can some one help me with macro or vba code to delete the row when column B,,C,D, and E all 4 have the value of 0 (It has to be 0 in all 4 column). Its very big sheet with thousands of rows. Please help Thank And |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row when zero
Hi Andy,
Try: Sub Tester() Dim i As Long Dim LRow As Long Dim sh As Worksheet ' Change the sheet name to your sheet name! Set sh = ActiveWorkbook.Sheets("Sheet1") LRow = LastRow(sh) For i = LRow To 7 Step -1 With sh If Application.Max(.Cells(i, 2).Resize(1, 4)) = 0 And _ Application.Min(.Cells(i, 2).Resize(1, 4)) = 0 Then .Cells(i, 2).EntireRow.Delete End If End With Next i End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ after:=sh.Range("A1"), _ lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function --- Regards, Norman "Andy" wrote in message ... Hi Sorry All, Forget to tell you I dont want this code to look row 1 thru 6. Should start from row 7 and end at row till the data ends. Can some one help me with macro or vba code to delete the row when column B,,C,D, and E all 4 have the value of 0. (It has to be 0 in all 4 column). Its very big sheet with thousands of rows. Please help Thanks Andy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row when zero
Hi Andy,
Try: Sub Tester() Dim i As Long Dim LRow As Long Dim sh As Worksheet ' Change the sheet name to your sheet name! Set sh = ActiveWorkbook.Sheets("Sheet1") LRow = LastRow(sh) For i = LRow To 7 Step -1 With sh If Application.Max(.Cells(i, 2).Resize(1, 4)) = 0 And _ Application.Min(.Cells(i, 2).Resize(1, 4)) = 0 Then .Cells(i, 2).EntireRow.Delete End If End With Next i End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ after:=sh.Range("A1"), _ lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function --- Regards, Norman "Andy" wrote in message ... Hi Sorry All, Forget to tell you I dont want this code to look row 1 thru 6. Should start from row 7 and end at row till the data ends. Can some one help me with macro or vba code to delete the row when column B,,C,D, and E all 4 have the value of 0. (It has to be 0 in all 4 column). Its very big sheet with thousands of rows. Please help Thanks Andy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row when zero
This
If Application.Max(.Cells(i, 2).Resize(1, 4)) = 0 And _ Application.Min(.Cells(i, 2).Resize(1, 4)) = 0 Then can be reduced to If Application.Countif(.Cells(i, 2).Resize(1, 4)),0) = 4 Then -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Norman Jones" wrote in message ... Hi Andy, Try: Sub Tester() Dim i As Long Dim LRow As Long Dim sh As Worksheet ' Change the sheet name to your sheet name! Set sh = ActiveWorkbook.Sheets("Sheet1") LRow = LastRow(sh) For i = LRow To 7 Step -1 With sh If Application.Max(.Cells(i, 2).Resize(1, 4)) = 0 And _ Application.Min(.Cells(i, 2).Resize(1, 4)) = 0 Then .Cells(i, 2).EntireRow.Delete End If End With Next i End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ after:=sh.Range("A1"), _ lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function --- Regards, Norman "Andy" wrote in message ... Hi Sorry All, Forget to tell you I dont want this code to look row 1 thru 6. Should start from row 7 and end at row till the data ends. Can some one help me with macro or vba code to delete the row when column B,,C,D, and E all 4 have the value of 0. (It has to be 0 in all 4 column). Its very big sheet with thousands of rows. Please help Thanks Andy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row when zero
Hi
Why dont you use the Data--Filter--Autofilter option to filter out zeroes in all the 4 columns, and delete all rows which match the criteria? Sukhjeet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete the test values, but do not delete the formulas | Excel Discussion (Misc queries) | |||
How can I delete a macro when the Delete button is not active? | Excel Worksheet Functions | |||
How to Delete a Range in Closed Workbook (to Replace Delete Query) | Excel Discussion (Misc queries) | |||
How do i delete a macro in Excel 2003 when delete isn't highlight | Excel Discussion (Misc queries) | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions |