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 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

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

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