Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows with multiple columns with 0 value
On my spreadsheet, I want to delete rows that have 0 values in columns C
through N. If any of these columns have something other than 0, I need them to stay. Can someone help me with a macro that would do this? I found a post that would do this for 2 columns, but I couldn't figure out how to do it for more than 2 columns. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows with multiple columns with 0 value
You could just use a formula.
=if(countif(c2:n2,0)=12,"X","") So, if you have 0 in ALL columns between c and n, you will have an x. Otherwise you'll have a blank. then you filter by this column, select x and delete rows. "Tasha" wrote: On my spreadsheet, I want to delete rows that have 0 values in columns C through N. If any of these columns have something other than 0, I need them to stay. Can someone help me with a macro that would do this? I found a post that would do this for 2 columns, but I couldn't figure out how to do it for more than 2 columns. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows with multiple columns with 0 value
Option Explicit
Sub testme02() Dim wks As Worksheet Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long Dim myRng As Range Set wks = Worksheets("Sheet1") With wks FirstRow = 2 'headers in row 1??? LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 '12 columns is C:N '.resize(1,12) means 1 row by 12 columns Set myRng = .Cells(iRow, "C").Resize(1, 12) If Application.CountIf(myRng, 0) = myRng.Cells.Count Then .Rows(iRow).Delete End If Next iRow End With End Sub Tasha wrote: On my spreadsheet, I want to delete rows that have 0 values in columns C through N. If any of these columns have something other than 0, I need them to stay. Can someone help me with a macro that would do this? I found a post that would do this for 2 columns, but I couldn't figure out how to do it for more than 2 columns. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows with multiple columns with 0 value
I would rather use a macro, there are 100's of rows and that would take too
long even with filtering. Thanks though.... anyone else have a macro that would help? "Sean Timmons" wrote: You could just use a formula. =if(countif(c2:n2,0)=12,"X","") So, if you have 0 in ALL columns between c and n, you will have an x. Otherwise you'll have a blank. then you filter by this column, select x and delete rows. "Tasha" wrote: On my spreadsheet, I want to delete rows that have 0 values in columns C through N. If any of these columns have something other than 0, I need them to stay. Can someone help me with a macro that would do this? I found a post that would do this for 2 columns, but I couldn't figure out how to do it for more than 2 columns. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows with multiple columns with 0 value
Thanks Dave!!! to my rescue yet again :)
well, not sure what it's doing, but doesn't look like it's doing anything. The 0 rows aren't deleted. I click on the button I assigned the macro to and it doesn't do anything. Maybe I left out something in my description??? This is how my sheet is set up: A B C D E F ......................... N PHYNO PHYNAME JAN FEB MAR APR etc.. through DEC 000001 A.DOCTOR 123 0 141 264 ...ETC.... 000002 B.DOCTOR 98 2 4 82 .................. 000003 C.DOCTOR 0 0 0 0 .......... 0 000004 D.DOCTOR 0 4 0 0 ............. 0 so for this example, the only row that would be deleted would be for C.DOCTOR which in my sheet is row 5. Anything that has more than 0 stays. If your macro is set up like that, then I must have copied something over wrong. I changed my test tab to be Sheet1 so it would be the same sheet name.... is there something I'm not doing right? Sorry, I'm not real familiar with coding just yet, but I'm learning! "Dave Peterson" wrote: Option Explicit Sub testme02() Dim wks As Worksheet Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long Dim myRng As Range Set wks = Worksheets("Sheet1") With wks FirstRow = 2 'headers in row 1??? LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 '12 columns is C:N '.resize(1,12) means 1 row by 12 columns Set myRng = .Cells(iRow, "C").Resize(1, 12) If Application.CountIf(myRng, 0) = myRng.Cells.Count Then .Rows(iRow).Delete End If Next iRow End With End Sub Tasha wrote: On my spreadsheet, I want to delete rows that have 0 values in columns C through N. If any of these columns have something other than 0, I need them to stay. Can someone help me with a macro that would do this? I found a post that would do this for 2 columns, but I couldn't figure out how to do it for more than 2 columns. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows with multiple columns with 0 value
First, you could have changed the line of code so that it used the correct
worksheet name: Set wks = Worksheets("Sheet1") to Set wks = Worksheets("yoursheetnamegoeshere") Second, are you sure that the values in those cells are really 0? Maybe they're small numbers that are formatted to hide the decimal places???? And you're sure that you wanted 12 columns checked for 0's--not empty cells--really 0's, right? Tasha wrote: Thanks Dave!!! to my rescue yet again :) well, not sure what it's doing, but doesn't look like it's doing anything. The 0 rows aren't deleted. I click on the button I assigned the macro to and it doesn't do anything. Maybe I left out something in my description??? This is how my sheet is set up: A B C D E F ........................ N PHYNO PHYNAME JAN FEB MAR APR etc.. through DEC 000001 A.DOCTOR 123 0 141 264 ...ETC.... 000002 B.DOCTOR 98 2 4 82 .................. 000003 C.DOCTOR 0 0 0 0 .......... 0 000004 D.DOCTOR 0 4 0 0 ............. 0 so for this example, the only row that would be deleted would be for C.DOCTOR which in my sheet is row 5. Anything that has more than 0 stays. If your macro is set up like that, then I must have copied something over wrong. I changed my test tab to be Sheet1 so it would be the same sheet name.... is there something I'm not doing right? Sorry, I'm not real familiar with coding just yet, but I'm learning! "Dave Peterson" wrote: Option Explicit Sub testme02() Dim wks As Worksheet Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long Dim myRng As Range Set wks = Worksheets("Sheet1") With wks FirstRow = 2 'headers in row 1??? LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 '12 columns is C:N '.resize(1,12) means 1 row by 12 columns Set myRng = .Cells(iRow, "C").Resize(1, 12) If Application.CountIf(myRng, 0) = myRng.Cells.Count Then .Rows(iRow).Delete End If Next iRow End With End Sub Tasha wrote: On my spreadsheet, I want to delete rows that have 0 values in columns C through N. If any of these columns have something other than 0, I need them to stay. Can someone help me with a macro that would do this? I found a post that would do this for 2 columns, but I couldn't figure out how to do it for more than 2 columns. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows with multiple columns with 0 value
Try the below. All cells should be zero (not blank)
Sub Deleterows() For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 If WorksheetFunction.CountIf(Range("C" & lngRow & ":N" & lngRow), "0") _ = 12 Then Rows(lngRow).Delete Next End Sub If this post helps click Yes --------------- Jacob Skaria "Tasha" wrote: On my spreadsheet, I want to delete rows that have 0 values in columns C through N. If any of these columns have something other than 0, I need them to stay. Can someone help me with a macro that would do this? I found a post that would do this for 2 columns, but I couldn't figure out how to do it for more than 2 columns. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows with multiple columns with 0 value
That worked. The other one may have worked also, I finally figured out that
the July through December columns weren't pulling anything so they were blank instead of 0's.... I filled all those in with 0's, reran macro and it worked.... Thanks for your help!!! "Jacob Skaria" wrote: Try the below. All cells should be zero (not blank) Sub Deleterows() For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 If WorksheetFunction.CountIf(Range("C" & lngRow & ":N" & lngRow), "0") _ = 12 Then Rows(lngRow).Delete Next End Sub If this post helps click Yes --------------- Jacob Skaria "Tasha" wrote: On my spreadsheet, I want to delete rows that have 0 values in columns C through N. If any of these columns have something other than 0, I need them to stay. Can someone help me with a macro that would do this? I found a post that would do this for 2 columns, but I couldn't figure out how to do it for more than 2 columns. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows with multiple columns with 0 value
I finally figured out that the July through December columns weren't pulling anything so they were blank instead of 0's.... I filled all those in with 0's, reran macro and it worked.... Thanks for your help!!! "Dave Peterson" wrote: First, you could have changed the line of code so that it used the correct worksheet name: Set wks = Worksheets("Sheet1") to Set wks = Worksheets("yoursheetnamegoeshere") Second, are you sure that the values in those cells are really 0? Maybe they're small numbers that are formatted to hide the decimal places???? And you're sure that you wanted 12 columns checked for 0's--not empty cells--really 0's, right? Tasha wrote: Thanks Dave!!! to my rescue yet again :) well, not sure what it's doing, but doesn't look like it's doing anything. The 0 rows aren't deleted. I click on the button I assigned the macro to and it doesn't do anything. Maybe I left out something in my description??? This is how my sheet is set up: A B C D E F ........................ N PHYNO PHYNAME JAN FEB MAR APR etc.. through DEC 000001 A.DOCTOR 123 0 141 264 ...ETC.... 000002 B.DOCTOR 98 2 4 82 .................. 000003 C.DOCTOR 0 0 0 0 .......... 0 000004 D.DOCTOR 0 4 0 0 ............. 0 so for this example, the only row that would be deleted would be for C.DOCTOR which in my sheet is row 5. Anything that has more than 0 stays. If your macro is set up like that, then I must have copied something over wrong. I changed my test tab to be Sheet1 so it would be the same sheet name.... is there something I'm not doing right? Sorry, I'm not real familiar with coding just yet, but I'm learning! "Dave Peterson" wrote: Option Explicit Sub testme02() Dim wks As Worksheet Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long Dim myRng As Range Set wks = Worksheets("Sheet1") With wks FirstRow = 2 'headers in row 1??? LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 '12 columns is C:N '.resize(1,12) means 1 row by 12 columns Set myRng = .Cells(iRow, "C").Resize(1, 12) If Application.CountIf(myRng, 0) = myRng.Cells.Count Then .Rows(iRow).Delete End If Next iRow End With End Sub Tasha wrote: On my spreadsheet, I want to delete rows that have 0 values in columns C through N. If any of these columns have something other than 0, I need them to stay. Can someone help me with a macro that would do this? I found a post that would do this for 2 columns, but I couldn't figure out how to do it for more than 2 columns. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows with more or less than 7 columns | New Users to Excel | |||
How do I delete rows and multiple columns automatically? | Excel Discussion (Misc queries) | |||
Proper procedures to delete multiple columns with 47,000 rows of data | Excel Discussion (Misc queries) | |||
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) | |||
Delete columns or rows | Excel Worksheet Functions |