Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to delete rows where cell value is zero
I am working in an Excel sheet where data is dumped from another system.
Many of the rows are extraneous - if the cell in the Current Hours column (AB) contains a zero value, I'd like my macro to delete the row. Someone here at my office knows more VBA than I do (which is very little) and we came up with the following. It is NOT working correctly. The current sheet of test data contains 575 rows. Of these, 24 have values greater than zero in column AB. 551 rows contain a zero in AB. When I run the macro, it does not seem to actually be using the values in AB to determine what to delete. Depending on which cell is active when I start, sometimes there are values 0 in AB, other times they are all = 0. I end up with a different number of rows depending on where I start. Here is the code of the macro. If anyone can tell me how to make the macro look at the contents of the cells in column AB and delete when they contain 0, I would really appreciate it. Thanks very much for your help. Sub DelBlankCurHrsRows() ' ' DelBlankCurHrsRows Macro ' Macro recorded 10/18/2006 by Ann Scharpf ' ' Keyboard Shortcut: Ctrl+Shift+E ' For Each c In Worksheets("Paste all employees' data here").Range("AB2:AB41000") If c.Value = "0" Then Selection.EntireRow.Delete End If Next End Sub -- Ann Scharpf |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to delete rows where cell value is zero
Ann
When you are looping through a range and deleting rows, you want to be sure you loop from the bottom up rather than from the top down. This macro will do what you want, but your "blank" cells in Column AB must be actually blank. I know you say Huh? at this point but when data is brought into Excel from an external source, problems may come with the data. For instance, a "blank" cell may have a space in it. That cell is not blank. It is an occupied cell. I included code in this macro to Trim (remove all extraneous spaces) all the cells in Column AB. Come back if this macro does not delete ALL the "blank" cells. HTH Otto Sub RemoveBlank() Dim RngColAB As Range Dim c As Long Application.ScreenUpdating = False Set RngColAB = Range("AB2", Range("AB" & Rows.Count).End(xlUp)) For c = RngColAB.Count To 1 Step -1 RngColAB(c).Value = Application.Trim(RngColAB(c)) If IsEmpty(RngColAB(c).Value) Then _ RngColAB(c).EntireRow.Delete Next c Application.ScreenUpdating = True End Sub "Ann Scharpf" wrote in message ... I am working in an Excel sheet where data is dumped from another system. Many of the rows are extraneous - if the cell in the Current Hours column (AB) contains a zero value, I'd like my macro to delete the row. Someone here at my office knows more VBA than I do (which is very little) and we came up with the following. It is NOT working correctly. The current sheet of test data contains 575 rows. Of these, 24 have values greater than zero in column AB. 551 rows contain a zero in AB. When I run the macro, it does not seem to actually be using the values in AB to determine what to delete. Depending on which cell is active when I start, sometimes there are values 0 in AB, other times they are all = 0. I end up with a different number of rows depending on where I start. Here is the code of the macro. If anyone can tell me how to make the macro look at the contents of the cells in column AB and delete when they contain 0, I would really appreciate it. Thanks very much for your help. Sub DelBlankCurHrsRows() ' ' DelBlankCurHrsRows Macro ' Macro recorded 10/18/2006 by Ann Scharpf ' ' Keyboard Shortcut: Ctrl+Shift+E ' For Each c In Worksheets("Paste all employees' data here").Range("AB2:AB41000") If c.Value = "0" Then Selection.EntireRow.Delete End If Next End Sub -- Ann Scharpf |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to delete rows where cell value is zero
Hi, Otto:
Thanks for taking the time to try to help me. I always end up causing confusion when I try to "alter the facts" for brevity. Actually, this data is coming from other Excel sheets. We have 100 employees who fill out bi-weekly status reports. I have written macros to copy the region where an employee MIGHT have entered data (never know how many rows the employee will use week to week) and dump the data (Paste as values) into an aggregate sheet. Further macros are eliminating columns that are no longer needed and I would like to delete these rows that calculated zero totals for the cumulative hours. The rows that I want to delete really DO contain a zero in the Cumulative Hours cell. I did add your code into my macros. When I run RemoveBlank, it doesn't seem to do anything at all. Was I supposed to add something before or after this code? Thanks again. Ann -- Ann Scharpf "Otto Moehrbach" wrote: Ann When you are looping through a range and deleting rows, you want to be sure you loop from the bottom up rather than from the top down. This macro will do what you want, but your "blank" cells in Column AB must be actually blank. I know you say Huh? at this point but when data is brought into Excel from an external source, problems may come with the data. For instance, a "blank" cell may have a space in it. That cell is not blank. It is an occupied cell. I included code in this macro to Trim (remove all extraneous spaces) all the cells in Column AB. Come back if this macro does not delete ALL the "blank" cells. HTH Otto Sub RemoveBlank() Dim RngColAB As Range Dim c As Long Application.ScreenUpdating = False Set RngColAB = Range("AB2", Range("AB" & Rows.Count).End(xlUp)) For c = RngColAB.Count To 1 Step -1 RngColAB(c).Value = Application.Trim(RngColAB(c)) If IsEmpty(RngColAB(c).Value) Then _ RngColAB(c).EntireRow.Delete Next c Application.ScreenUpdating = True End Sub "Ann Scharpf" wrote in message ... I am working in an Excel sheet where data is dumped from another system. Many of the rows are extraneous - if the cell in the Current Hours column (AB) contains a zero value, I'd like my macro to delete the row. Someone here at my office knows more VBA than I do (which is very little) and we came up with the following. It is NOT working correctly. The current sheet of test data contains 575 rows. Of these, 24 have values greater than zero in column AB. 551 rows contain a zero in AB. When I run the macro, it does not seem to actually be using the values in AB to determine what to delete. Depending on which cell is active when I start, sometimes there are values 0 in AB, other times they are all = 0. I end up with a different number of rows depending on where I start. Here is the code of the macro. If anyone can tell me how to make the macro look at the contents of the cells in column AB and delete when they contain 0, I would really appreciate it. Thanks very much for your help. Sub DelBlankCurHrsRows() ' ' DelBlankCurHrsRows Macro ' Macro recorded 10/18/2006 by Ann Scharpf ' ' Keyboard Shortcut: Ctrl+Shift+E ' For Each c In Worksheets("Paste all employees' data here").Range("AB2:AB41000") If c.Value = "0" Then Selection.EntireRow.Delete End If Next End Sub -- Ann Scharpf |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to delete rows where cell value is zero
I reread your message and changed the code as follows:
If IsEmpty(RngColAB(c).Value) Then _ changed to If RngColAB(c).Value = 0 Then _ This made the macro work. It deletes all but the 24 rows that have a value 0. Thanks very much for your help. Now I will READ the code and try to understand it! -- Ann Scharpf "Otto Moehrbach" wrote: Ann When you are looping through a range and deleting rows, you want to be sure you loop from the bottom up rather than from the top down. This macro will do what you want, but your "blank" cells in Column AB must be actually blank. I know you say Huh? at this point but when data is brought into Excel from an external source, problems may come with the data. For instance, a "blank" cell may have a space in it. That cell is not blank. It is an occupied cell. I included code in this macro to Trim (remove all extraneous spaces) all the cells in Column AB. Come back if this macro does not delete ALL the "blank" cells. HTH Otto Sub RemoveBlank() Dim RngColAB As Range Dim c As Long Application.ScreenUpdating = False Set RngColAB = Range("AB2", Range("AB" & Rows.Count).End(xlUp)) For c = RngColAB.Count To 1 Step -1 RngColAB(c).Value = Application.Trim(RngColAB(c)) If IsEmpty(RngColAB(c).Value) Then _ RngColAB(c).EntireRow.Delete Next c Application.ScreenUpdating = True End Sub "Ann Scharpf" wrote in message ... I am working in an Excel sheet where data is dumped from another system. Many of the rows are extraneous - if the cell in the Current Hours column (AB) contains a zero value, I'd like my macro to delete the row. Someone here at my office knows more VBA than I do (which is very little) and we came up with the following. It is NOT working correctly. The current sheet of test data contains 575 rows. Of these, 24 have values greater than zero in column AB. 551 rows contain a zero in AB. When I run the macro, it does not seem to actually be using the values in AB to determine what to delete. Depending on which cell is active when I start, sometimes there are values 0 in AB, other times they are all = 0. I end up with a different number of rows depending on where I start. Here is the code of the macro. If anyone can tell me how to make the macro look at the contents of the cells in column AB and delete when they contain 0, I would really appreciate it. Thanks very much for your help. Sub DelBlankCurHrsRows() ' ' DelBlankCurHrsRows Macro ' Macro recorded 10/18/2006 by Ann Scharpf ' ' Keyboard Shortcut: Ctrl+Shift+E ' For Each c In Worksheets("Paste all employees' data here").Range("AB2:AB41000") If c.Value = "0" Then Selection.EntireRow.Delete End If Next End Sub -- Ann Scharpf |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to delete rows where cell value is zero
Otto...
Not sure if I posted this correctly last time.. but I was wanting to take the macro here one step further & have it so that if I highlight any column in any spreadsheet that it would remove any rows with zero value in that column... I had macros before that simply utilised autofilter but with Excel 2003 when using large files excel just seems to hang Can you give me some guidance.. Much Appreciated Robin Otto Moehrbach wrote: Ann When you are looping through a range and deleting rows, you want to be sure you loop from the bottom up rather than from the top down. This macro will do what you want, but your "blank" cells in Column AB must be actually blank. I know you say Huh? at this point but when data is brought into Excel from an external source, problems may come with the data. For instance, a "blank" cell may have a space in it. That cell is not blank. It is an occupied cell. I included code in this macro to Trim (remove all extraneous spaces) all the cells in Column AB. Come back if this macro does not delete ALL the "blank" cells. HTH Otto Sub RemoveBlank() Dim RngColAB As Range Dim c As Long Application.ScreenUpdating = False Set RngColAB = Range("AB2", Range("AB" & Rows.Count).End(xlUp)) For c = RngColAB.Count To 1 Step -1 RngColAB(c).Value = Application.Trim(RngColAB(c)) If IsEmpty(RngColAB(c).Value) Then _ RngColAB(c).EntireRow.Delete Next c Application.ScreenUpdating = True End Sub "Ann Scharpf" wrote in message ... I am working in an Excel sheet where data is dumped from another system. Many of the rows are extraneous - if the cell in the Current Hours column (AB) contains a zero value, I'd like my macro to delete the row. Someone here at my office knows more VBA than I do (which is very little) and we came up with the following. It is NOT working correctly. The current sheet of test data contains 575 rows. Of these, 24 have values greater than zero in column AB. 551 rows contain a zero in AB. When I run the macro, it does not seem to actually be using the values in AB to determine what to delete. Depending on which cell is active when I start, sometimes there are values 0 in AB, other times they are all = 0. I end up with a different number of rows depending on where I start. Here is the code of the macro. If anyone can tell me how to make the macro look at the contents of the cells in column AB and delete when they contain 0, I would really appreciate it. Thanks very much for your help. Sub DelBlankCurHrsRows() ' ' DelBlankCurHrsRows Macro ' Macro recorded 10/18/2006 by Ann Scharpf ' ' Keyboard Shortcut: Ctrl+Shift+E ' For Each c In Worksheets("Paste all employees' data here").Range("AB2:AB41000") If c.Value = "0" Then Selection.EntireRow.Delete End If Next End Sub -- Ann Scharpf |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code to delete rows | Excel Discussion (Misc queries) | |||
Can this code be modified to delete rows? | Excel Programming | |||
code to delete rows | Excel Discussion (Misc queries) | |||
VB Code to Delete Unused Rows | Excel Programming | |||
Code to sort/delete rows | Excel Programming |