Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code help, delete rows based on column criteria
I have a macro, which I am sure will make the experts cringe or laughbut it
worked. My needs however have changed, and it needs to be smarter. Situation: I have multiple financial workbooks, each with 100s of lines, and 5-10 tabs. Each line is a different budgeted cost and each tab is a different cost center. Costs are budgeted for the next 5 years. Each line may not have a budgeted amount, but acts as a place holder for future budgeting needs. I have adapted Ron De Bruins ( http://www.rondebruin.nl/summary.htm) code that consolidates every tab in a workbook to a summary sheet. Once the info is consolidated to a summary sheet, I need to put it into a pivot table, but the data needs a little cleaning first. First, I deleted all rows that have an empty column a (another adapted Ron De Bruins code). Second, since not every budget item ( or row) will have data in it, I need to remove all of the rows that have no budgeted dollar amounts. If I dont remove these empty rows, it will really clutter the Pivot table. My code only deletes rows that have the total for 2007 as $-0-. It does this by inserting a line after the 2007 total populating a formula in that new row that divides the 2007 total into 0. If it result is #div/0, than the entire row is deleted. My problem: Sometimes there is no budgeted information in the year 2007, but there is in '08 - '11 (and/or vice versa). And this is where my makeshift code is no good because my code would still delete that entire line, where ideally it should remain if any of the cells containing an $ < 0 exists from the columns that represent Jan 2007 (Q) to Total 2011 (CC). Thank you in advance and any help would be greatly appreciated. --------------------------------------------------------------- Sub Step_FourDeleteLineswithZeroTotals() 'This macro deletes all lines with no budgeted information for the year 2007, 'by inserting a line after column "Z," which is the total column for '07, 'then fills in a formula, which divides zero by the total column. If the 'total column is a zero, then it will give an error message #/div. Then all 'rows that have an error message in that column are deleted. ' insert a column in "aa" copy formula and delete #div rows. Application.ScreenUpdating = False 'Insert column and formula Worksheets("Summary").Columns("aa").Insert Worksheets("Summary").Select Range("aa2").Select ActiveCell.Formula = "=0/z2" Worksheets("summary").Range("aa2:aa45000").FillDow n 'Delete any row that has an error message in row "aa" Dim rng As Range On Error Resume Next Set rng = Columns("aa").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End If Worksheets("summary").Columns("aa").Delete Application.ScreenUpdating = True Dim r As Range Count = 0 For Each r In ActiveSheet.UsedRange If Application.IsText(r.Value) Then If IsNumeric(r.Value) Then r.Value = 1# * r.Value r.numberformat = "General" Count = Count + 1 End If End If Next End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code help, delete rows based on column criteria
On 19 Mar, 20:21, Stout wrote:
I have a macro, which I am sure will make the experts cringe or laugh...but it worked. My needs however have changed, and it needs to be "smarter." Situation: I have multiple financial workbooks, each with 100s of lines, and 5-10 tabs. Each line is a different budgeted cost and each tab is a different cost center. Costs are budgeted for the next 5 years. Each line may not have a budgeted amount, but acts as a place holder for future budgeting needs. I have adapted Ron De Bruin's (http://www.rondebruin.nl/summary.htm) code that consolidates every tab in a workbook to a summary sheet. Once the info is consolidated to a summary sheet, I need to put it into a pivot table, but the data needs a little cleaning first. First, I deleted all rows that have an empty column 'a' (another adapted Ron De Bruin's code). Second, since not every budget item ( or row) will have data in it, I need to remove all of the rows that have no budgeted dollar amounts. If I don't remove these empty rows, it will really clutter the Pivot table. My code only deletes rows that have the total for 2007 as $-0-. It does this by inserting a line after the "2007 total" populating a formula in that new row that divides the 2007 total into 0. If it result is '#div/0', than the entire row is deleted. My problem: Sometimes there is no budgeted information in the year 2007, but there is in '08 - '11 (and/or vice versa). And this is where my makeshift code is no good because my code would still delete that entire line, where ideally it should remain if any of the cells containing an $ < 0 exists from the columns that represent Jan 2007 ('Q') to Total 2011 ('CC'). Thank you in advance and any help would be greatly appreciated. --------------------------------------------------------------- Sub Step_FourDeleteLineswithZeroTotals() 'This macro deletes all lines with no budgeted information for the year 2007, 'by inserting a line after column "Z," which is the total column for '07, 'then fills in a formula, which divides zero by the total column. If the 'total column is a zero, then it will give an error message #/div. Then all 'rows that have an error message in that column are deleted. ' insert a column in "aa" copy formula and delete #div rows. Application.ScreenUpdating = False 'Insert column and formula Worksheets("Summary").Columns("aa").Insert Worksheets("Summary").Select Range("aa2").Select ActiveCell.Formula = "=0/z2" Worksheets("summary").Range("aa2:aa45000").FillDow n 'Delete any row that has an error message in row "aa" Dim rng As Range On Error Resume Next Set rng = Columns("aa").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End If Worksheets("summary").Columns("aa").Delete Application.ScreenUpdating = True Dim r As Range Count = 0 For Each r In ActiveSheet.UsedRange If Application.IsText(r.Value) Then If IsNumeric(r.Value) Then r.Value = 1# * r.Value r.numberformat = "General" Count = Count + 1 End If End If Next End Sub Without changing too much of the code, I would suggest changing the line that evaluates an error: Range("aa2").Select ActiveCell.Formula = "=0/z2" to include the other columns that you need to take into account: Range("aa2").Select ActiveCell.Formula = "=0/(z2+al2+az2+bl2+bz2+cc2)" (or whatever the correct references to pickup the totals from 2008, 2009, ..... need to be) This way, if 2007 is 0, but 2008 is not 0, the total denominator will not be 0, and the formula should not return an error, so the row should not be deleted. HTH Andrew |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code help, delete rows based on column criteria
A great and simple solution, I feel that I should have thought of that :)
Much thanks! "loudfish" wrote: On 19 Mar, 20:21, Stout wrote: I have a macro, which I am sure will make the experts cringe or laugh...but it worked. My needs however have changed, and it needs to be "smarter." Situation: I have multiple financial workbooks, each with 100s of lines, and 5-10 tabs. Each line is a different budgeted cost and each tab is a different cost center. Costs are budgeted for the next 5 years. Each line may not have a budgeted amount, but acts as a place holder for future budgeting needs. I have adapted Ron De Bruin's (http://www.rondebruin.nl/summary.htm) code that consolidates every tab in a workbook to a summary sheet. Once the info is consolidated to a summary sheet, I need to put it into a pivot table, but the data needs a little cleaning first. First, I deleted all rows that have an empty column 'a' (another adapted Ron De Bruin's code). Second, since not every budget item ( or row) will have data in it, I need to remove all of the rows that have no budgeted dollar amounts. If I don't remove these empty rows, it will really clutter the Pivot table. My code only deletes rows that have the total for 2007 as $-0-. It does this by inserting a line after the "2007 total" populating a formula in that new row that divides the 2007 total into 0. If it result is '#div/0', than the entire row is deleted. My problem: Sometimes there is no budgeted information in the year 2007, but there is in '08 - '11 (and/or vice versa). And this is where my makeshift code is no good because my code would still delete that entire line, where ideally it should remain if any of the cells containing an $ < 0 exists from the columns that represent Jan 2007 ('Q') to Total 2011 ('CC'). Thank you in advance and any help would be greatly appreciated. --------------------------------------------------------------- Sub Step_FourDeleteLineswithZeroTotals() 'This macro deletes all lines with no budgeted information for the year 2007, 'by inserting a line after column "Z," which is the total column for '07, 'then fills in a formula, which divides zero by the total column. If the 'total column is a zero, then it will give an error message #/div. Then all 'rows that have an error message in that column are deleted. ' insert a column in "aa" copy formula and delete #div rows. Application.ScreenUpdating = False 'Insert column and formula Worksheets("Summary").Columns("aa").Insert Worksheets("Summary").Select Range("aa2").Select ActiveCell.Formula = "=0/z2" Worksheets("summary").Range("aa2:aa45000").FillDow n 'Delete any row that has an error message in row "aa" Dim rng As Range On Error Resume Next Set rng = Columns("aa").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End If Worksheets("summary").Columns("aa").Delete Application.ScreenUpdating = True Dim r As Range Count = 0 For Each r In ActiveSheet.UsedRange If Application.IsText(r.Value) Then If IsNumeric(r.Value) Then r.Value = 1# * r.Value r.numberformat = "General" Count = Count + 1 End If End If Next End Sub Without changing too much of the code, I would suggest changing the line that evaluates an error: Range("aa2").Select ActiveCell.Formula = "=0/z2" to include the other columns that you need to take into account: Range("aa2").Select ActiveCell.Formula = "=0/(z2+al2+az2+bl2+bz2+cc2)" (or whatever the correct references to pickup the totals from 2008, 2009, ..... need to be) This way, if 2007 is 0, but 2008 is not 0, the total denominator will not be 0, and the formula should not return an error, so the row should not be deleted. HTH Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows based on criteria in excel | Excel Discussion (Misc queries) | |||
How can I delete rows programmatically based on certain criteria? | New Users to Excel | |||
How can I delete rows programmatically based on certain criteria? | Excel Worksheet Functions | |||
Delete rows based on criteria | Excel Discussion (Misc queries) | |||
Delete rows based on certain criteria | Excel Discussion (Misc queries) |