Delete Rows using a Macro

jrhguez

New Member
Joined
May 8, 2008
Messages
48
from the report below I would like to create a macro that deletes all rows that make up the Total Amounts. In other words, I just want to show the total (bold on black)...thank you

CategoryOriginal BudgetJV Change OrderRevised BudgetCommitedOther CostsCurrent ProjectionCost Incurred to DateBalance CompleteVariance Current MonthVariance Project to DateCost Per UnitCost Per Sq. Ft.% Complete
GENERAL CONDITIONS
Construction Payroll1,925,000.00277,052.002,202,052.000.002,892,778.572,892,778.572,878,247.3714,531.20(35,000.00)690,726.579,242.106.4199.50
Construction Travel20,000.000.0020,000.000.0043,943.8643,943.8643,943.860.000.0023,943.86140.400.10100.00
Relocation Moving Expenses0.000.000.000.005,651.315,651.315,651.310.000.005,651.3118.060.01100.00
Recruiting Costs60,000.000.0060,000.000.00104,662.74104,662.74104,662.740.000.0044,662.74334.390.23100.00
Office Supplies19,200.000.0019,200.000.0023,680.8223,680.8223,680.820.000.004,480.8275.660.05100.00
Postage & Delivery8,400.000.008,400.000.004,776.924,776.924,776.920.000.00(3,623.08)15.260.01100.00
Cell Phones21,600.000.0021,600.000.0022,147.8622,147.8622,147.860.000.00547.8670.760.05100.00
Relations24,000.000.0024,000.000.007,782.697,782.697,782.690.000.00(16,217.31)24.860.02100.00
Legal Fees20,000.000.0020,000.000.00147,818.60147,818.60147,818.600.000.00127,818.60472.260.33100.00
Plans & Printing30,000.008,000.0038,000.000.0033,090.5833,090.5833,090.580.000.00(4,909.42)105.720.07100.00
Office Trailers & Storage62,700.000.0062,700.00586.2239,705.7640,291.9840,291.980.000.00(22,408.02)128.730.09100.00
Office Equipment35,000.000.0035,000.000.0061,890.9161,890.9161,890.910.000.0026,890.91197.730.14100.00
Office Electricity8,400.000.008,400.000.0011,916.7111,916.7111,916.710.000.003,516.7138.070.03100.00
Office Water8,776.000.008,776.000.009,810.089,810.089,810.080.000.001,034.0831.340.02100.00
TOTAL GENERAL CONDITIONS2,243,076.00285,052.002,528,128.00586.223,409,657.413,410,243.633,395,712.4314,531.20(35,000.00)882,115.6310,895.357.5599.57
SITE WORK
Miscellaneous Site Work70,000.000.0070,000.0063,392.397,701.7771,094.1671,094.160.000.001,094.16227.140.16100.00
Street Lights0.000.000.000.0066,500.1866,500.1866,500.180.000.0066,500.18212.460.15100.00
Earthwork1,682,348.00161,493.611,843,841.612,113,768.72250.002,114,018.722,114,018.720.000.00270,177.116,754.054.68100.00
Shoring & Underpinning25,000.000.0025,000.0050,572.160.0050,572.1650,572.160.000.0025,572.16161.570.11100.00
Piling152,274.000.00152,274.00119,832.000.00119,832.00119,832.000.000.00(32,442.00)382.850.27100.00
Asphalt Paving191,931.000.00191,931.00248,651.00102.37248,753.37248,753.370.000.0056,822.37794.740.55100.00
Utilities Relocation300,000.000.00300,000.000.00353,465.82353,465.82353,465.820.000.0053,465.821,129.280.78100.00
TOTAL SITE WORK2,421,553.00161,493.612,583,046.612,596,216.27428,020.143,024,236.413,024,236.410.000.00441,189.809,662.106.70100.00
LANDSCAPING
Miscellaneous Site Work1,288,740.00156,461.501,445,201.501,499,332.455,579.861,504,912.311,504,912.310.000.0059,710.814,808.033.33100.00
Pool & Spa114,300.000.00114,300.00121,300.000.00121,300.00121,300.000.000.007,000.00387.540.27100.00
Landscaping200,000.00166,215.22366,215.22394,460.224,992.00399,452.22399,452.220.000.0033,237.001,276.210.88100.00
TOTAL LANDSCAPING1,603,040.00322,676.721,925,716.722,015,092.6710,571.862,025,664.532,025,664.530.000.0099,947.816,471.774.49100.00
APARTMENT STRUCTURES
Final Cleaning83,102.003,500.0086,602.00127,873.0013,575.71141,448.71141,448.710.000.0054,846.71451.910.31100.00
Cast in Place Concrete994,841.000.00994,841.001,321,353.2079,064.151,400,417.351,400,417.350.000.00405,576.354,474.183.10100.00
PT & Reinforcing Steel372,461.003,600.00376,061.00373,395.2448,385.74421,780.98421,780.980.000.0045,719.981,347.540.93100.00
Cast in Place Concrte-Material1,105,171.004,000.001,109,171.001,406,719.3524,416.281,431,135.631,431,135.630.000.00321,964.634,572.323.17100.00
Post Tensioned Cable Mat238,700.000.00238,700.00141,985.910.00141,985.91141,985.910.000.00(96,714.09)453.630.31100.00
Formwork Cast in Plac Con1,180,746.000.001,180,746.001,188,195.681,142.761,189,338.441,189,338.440.000.008,592.443,799.802.63100.00
Place and Finish Labor38,033.000.0038,033.0038,033.000.0038,033.0038,033.000.000.000.00121.510.08100.00
Concrete Pump565,165.000.00565,165.00595,376.57161,622.12756,998.69756,998.690.000.00191,833.692,418.531.68100.00
PT & Reinforc Steel Instal275,735.000.00275,735.00285,635.001,203.75286,838.75286,838.750.000.0011,103.75916.420.64100.00
Gypsum Cement Underlayment589,012.000.00589,012.00748,928.000.00748,928.00748,928.000.000.00159,916.002,392.741.66100.00
Concrete Fill Materials0.000.000.000.00222,797.09222,797.0956,476.09166,321.00166,321.00222,797.09711.810.4925.35
Brick Masonry0.000.000.000.00314,008.00314,008.00322,472.52(8,464.52)60,500.00314,008.001,003.220.70102.70
Concrete Unit Masonry1,448,718.0090,918.001,539,636.001,591,184.3736.181,591,220.551,591,184.3736.180.0051,584.555,083.773.52100.00
Metal Railings495,668.000.00495,668.00395,510.680.00395,510.68395,510.680.000.00(100,157.32)1,263.610.88100.00
Misc. Metals854,842.00119,535.00974,377.00720,757.87247,005.74967,763.61967,763.610.000.00(6,613.39)3,091.902.14100.00
Rough Carpentry5,511,701.00122,942.495,634,643.495,153,875.01482,823.305,636,698.315,636,698.310.000.002,054.8218,008.6212.48100.00
Interior Finish Carpentry181,606.0024,398.00206,004.00863,760.960.00863,760.96863,760.960.000.00657,756.962,759.621.91100.00
Waterproofing & Dampproofing1,368,226.00800.001,369,026.001,138,583.3528,162.821,166,746.171,166,746.170.000.00(202,279.83)3,727.622.58100.00
Preventative Coating/Remediati77,624.000.0077,624.0093,662.000.0093,662.0093,662.000.000.0016,038.00299.240.21100.00
Insulation524,728.000.00524,728.00703,255.001,180.51704,435.51704,435.510.000.00179,707.512,250.591.56100.00
Roofing879,281.0038,458.00917,739.00545,506.0017,100.00562,606.00562,606.000.000.00(355,133.00)1,797.461.25100.00
Siding1,136,736.0016,250.001,152,986.00968,209.055,929.83974,138.88974,138.880.000.00(178,847.12)3,112.262.16100.00
Fire stopping45,000.000.0045,000.000.007,579.597,579.597,579.590.000.00(37,420.41)24.220.02100.00
Overhead Coiling Doors61,896.0083,148.00145,044.00159,491.00497.25159,988.25159,988.250.000.0014,944.25511.140.35100.00
Entrance & Storefront347,553.001,800.00349,353.00469,927.281,563.34471,490.62471,490.620.000.00122,137.621,506.361.04100.00
Windows673,323.000.00673,323.00968,699.730.00968,699.73968,699.730.000.00295,376.733,094.892.15100.00
Door Hardware1,142,765.006,260.001,149,025.00775,713.54817.20776,530.74776,530.740.000.00(372,494.26)2,480.931.72100.00
Gypsum Board2,622,960.00160,942.002,783,902.003,134,502.00888.943,135,390.943,135,390.940.000.00351,488.9410,017.226.94100.00
Tile/Stone1,331,925.00158,440.001,490,365.001,468,505.001,219.691,469,724.691,469,724.690.000.00(20,640.31)4,695.613.26100.00
Painting755,456.0031,500.00786,956.001,112,475.110.001,112,475.111,112,475.110.000.00325,519.113,554.232.46100.00
Signage45,000.000.0045,000.000.0017,971.0017,971.0017,971.000.000.00(27,029.00)57.420.04100.00
Toilet/Bath Accessories241,843.0026,530.00268,373.00131,851.00269.66132,120.66132,120.660.000.00(136,252.34)422.110.29100.00
Fireplaces & Accessories35,000.000.0035,000.000.0068,745.0068,745.0068,745.000.000.0033,745.00219.630.15100.00
Storage Units54,000.000.0054,000.000.000.000.000.000.000.00(54,000.00)0.000.000.00
Postal Specialties72,000.000.0072,000.0043,370.000.0043,370.0043,370.000.000.00(28,630.00)138.560.10100.00
Misc. Specialties10,000.003,500.0013,500.000.0013,393.5913,393.5913,393.590.000.00(106.41)42.790.03100.00
Residential Appliances837,531.000.00837,531.00914,138.0524,682.82938,820.87938,820.870.000.00101,289.872,999.432.08100.00
Window Treatments150,723.000.00150,723.00110,200.000.00110,200.00110,200.000.000.00(40,523.00)352.080.24100.00
Residential Casework772,090.0037,488.00809,578.00782,136.000.00782,136.00782,136.000.000.00(27,442.00)2,498.841.73100.00
Stone/Tile Countertops305,575.0011,502.00317,077.00386,586.000.00386,586.00386,586.000.000.0069,509.001,235.100.86100.00
Elevators902,746.003,984.00906,730.001,060,554.088,532.861,069,086.941,069,086.940.000.00162,356.943,415.612.37100.00
Trash Chutes101,602.001,905.00103,507.00109,641.000.00109,641.00109,641.000.000.006,134.00350.290.24100.00
Fire Sprinkler System780,698.0028,000.00808,698.00879,000.002,805.49881,805.49881,805.490.000.0073,107.492,817.271.95100.00
Water Submetering42,255.000.0042,255.0035,967.000.0035,967.0035,967.000.000.00(6,288.00)114.910.08100.00
Plumbing System2,474,823.0047,278.632,522,101.632,471,859.4869,025.642,540,885.122,540,885.120.000.0018,783.498,117.845.63100.00
HVAC System1,863,910.0010,018.001,873,928.001,829,186.0619,420.691,848,606.751,848,606.750.000.00(25,321.25)5,906.094.09100.00
Electrical4,103,361.00637,001.474,740,362.475,660,906.654,841.495,665,748.145,665,748.140.000.00925,385.6718,101.4312.55100.00
Electrical Switch Gear0.0046,495.0046,495.000.000.000.000.000.000.00(46,495.00)0.000.000.00
Access Control System151,816.000.00151,816.000.000.000.000.000.000.00(151,816.00)0.000.000.00
TOTAL APARTMENT STRUCTURES37,847,947.001,720,193.5939,568,140.5940,906,508.221,890,708.2342,797,216.4542,639,323.79157,892.66226,821.003,229,075.86136,732.3294.7999.63
LEASING OFFICE/CLUBHOUSE
Architectural Woodwork247,640.000.00247,640.00278,608.8513,556.76292,165.61292,165.610.000.0044,525.61933.440.65100.00
TOTAL LEASING OFFICE/CLUBHOUSE247,640.000.00247,640.00278,608.8513,556.76292,165.61292,165.610.000.0044,525.61933.440.65100.00
GARAGE/PARKING STRUCTURE
Precast Structural Concrete2,907,500.000.002,907,500.002,872,150.000.002,872,150.002,872,150.000.000.00(35,350.00)9,176.206.36100.00
TOTAL GARAGE/PARKING STRUCTURE2,907,500.000.002,907,500.002,872,150.000.002,872,150.002,872,150.000.000.00(35,350.00)9,176.206.36100.00
STAGING & LOGISTICS
Punch Carpenter109,550.000.00109,550.000.00159,930.99159,930.99159,930.990.000.0050,380.99510.960.35100.00
General Labor82,548.002,040.0084,588.000.00481,074.57481,074.57481,074.570.000.00396,486.571,536.981.07100.00
Backcharge/Damage & Correction0.000.000.000.003,845.203,845.203,845.200.000.003,845.2012.280.01100.00
Technology Fees & Licenses30,000.000.0030,000.000.0028,113.1728,113.1728,113.170.000.00(1,886.83)89.820.06100.00
Professional Scheduling0.000.000.000.00112,940.00112,940.00112,940.000.000.00112,940.00360.830.25100.00
Safety Inspections24,000.000.0024,000.000.0013,425.2613,425.2613,425.260.000.00(10,574.74)42.890.03100.00
Temporary Electric70,000.0058,087.34128,087.340.00336,331.03336,331.03336,331.030.000.00208,243.691,074.540.74100.00
Temp HVAC50,000.000.0050,000.000.001,436.071,436.071,436.070.000.00(48,563.93)4.590.00100.00
Temp Storage/Trailer2,500.000.002,500.000.000.000.000.000.000.00(2,500.00)0.000.000.00
Sanitary Facilities55,000.000.0055,000.000.0074,754.1474,754.1474,754.140.000.0019,754.14238.830.17100.00
Misc. Tools & Materials15,000.000.0015,000.000.00172,162.24172,162.24172,162.240.000.00157,162.24550.040.38100.00
Equipment Owned10,000.000.0010,000.000.000.000.000.000.000.00(10,000.00)0.000.000.00
Equipment Rented60,000.0050,433.00110,433.00103,256.98456,892.34560,149.32560,149.320.000.00449,716.321,789.611.24100.00
Construction Trash Chutes12,000.000.0012,000.000.006,433.016,433.016,433.010.000.00(5,566.99)20.550.01100.00
Safety Equipment & Services12,000.000.0012,000.000.0042,974.2442,974.2442,974.240.000.0030,974.24137.300.10100.00
Temporary Fencing25,000.000.0025,000.000.0044,999.5044,999.5044,999.500.000.0019,999.50143.770.10100.00
Temp Environmental Controls30,000.000.0030,000.000.0076,285.4676,285.4676,285.460.000.0046,285.46243.720.17100.00
Temporary Project Signage4,500.000.004,500.000.001,900.511,900.511,900.510.000.00(2,599.49)6.070.00100.00
Progress Cleaning10,000.000.0010,000.000.005,128.405,128.405,128.400.000.00(4,871.60)16.380.01100.00
Construction Waste/Disposal234,750.00980.00235,730.000.00298,106.05298,106.05298,106.050.000.0062,376.05952.420.66100.00
Police Detail/Traffic Control57,000.000.0057,000.000.00421,922.87421,922.87421,922.870.000.00364,922.871,348.000.93100.00
Surveying30,000.002,200.0032,200.0089,835.009,987.8799,822.8799,822.870.000.0067,622.87318.920.22100.00
Special Inspections & Testing50,000.000.0050,000.00144,085.8821,926.41166,012.29166,012.290.000.00116,012.29530.390.37100.00
TOTAL STAGING & LOGISTICS973,848.00113,740.341,087,588.34337,177.862,770,569.333,107,747.193,107,747.190.000.002,020,158.859,928.906.88100.00
WARRANTY
Warranty91,800.000.0091,800.000.0051,000.0051,000.0042,135.468,864.540.00(40,800.00)162.940.1182.62
TOTAL WARRANTY91,800.000.0091,800.000.0051,000.0051,000.0042,135.468,864.540.00(40,800.00)162.940.1182.62
CONSTRUCTION INSURANCE
Builders Risk Insurance303,238.000.00303,238.000.00343,489.40343,489.40343,489.400.000.0040,251.401,097.410.76100.00
General Liability327,304.000.00327,304.000.00350,966.14350,966.14350,966.140.000.0023,662.141,121.300.78100.00
TOTAL CONSTRUCTION INSURANCE630,542.000.00630,542.000.00694,455.54694,455.54694,455.540.000.0063,913.542,218.711.54100.00
CONSTRUCTION FEES
Construction Fees2,127,143.0093,057.322,220,200.320.002,220,200.622,220,200.622,220,200.320.300.000.307,093.294.92100.00
TOTAL CONSTRUCTION FEES2,127,143.0093,057.322,220,200.320.002,220,200.622,220,200.622,220,200.320.300.000.307,093.294.92100.00
CONTINGENCY
Construction Contingency1,959,361.0031,251.111,990,612.110.000.000.000.000.000.00(1,990,612.11)0.000.000.00
TOTAL CONTINGENCY1,959,361.0031,251.111,990,612.110.000.000.000.000.000.00(1,990,612.11)0.000.000.00
Rebates(203,450.00)0.00(203,450.00)0.00(216,070.00)(216,070.00)(216,070.00)0.000.00(12,620.00)(690.32)(0.48)100.00
Reimbursements0.000.000.00139,460.41(375,830.68)(236,370.27)(236,370.27)0.000.00(236,370.27)(755.18)(0.52)100.00
TOTAL REBATES/REIMBURSEMENTS(203,450.00)0.00(203,450.00)139,460.41(591,900.68)(452,440.27)(452,440.27)0.000.00(248,990.27)(1,445.50)(1.00)100.00
TOTAL FOR JOB 350011 52,850,000.002,727,464.6955,577,464.6949,145,800.5010,896,839.2160,042,639.7159,861,351.01181,288.70191,821.004,465,175.02191,829.52132.9899.70

<colgroup><col><col span="13"></colgroup><tbody>
</tbody>
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,799
Office Version
  1. 365
  2. 2010
Trying to understand the logic here...

Without knowing what formulas are in the sample array, suppose the formulas are =SUM(the range of values in each respective category)

If the various total rows have SUM formulas (based on ranges), you're not deleting those rows (constants). However, if/when the formula-based rows are retained, won't the formulas be summing incorrect ranges?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

jrhguez

New Member
Joined
May 8, 2008
Messages
48
Hello again: I've been testing it out, but I just realized that is deleting the last two categories toward the bottom of the report..Any ideas?

TOTAL CONTINGENCY1,959,36131,2511,990,612000000(1,990,612)000
TOTAL REBATES/REIMBURSEMENTS(203,450)0(203,450)139,460(591,901)(452,440)(452,440)00(248,990)(1,445)(1)100

<colgroup><col><col span="13"></colgroup><tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,964
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Code:
   With Range("D2", Range("D" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(isnumber(search(""total"",@)),@,"""")", "@", .Address))
      .SpecialCells(xlBlanks).EntireRow.Delete
   End With
 

jrhguez

New Member
Joined
May 8, 2008
Messages
48
I was wondering if I could bother you with one last favor. Right now all rows read:
TOTAL WARRANTY
TOTAL CONSTRUCTION INSURANCE etc

How possible (for the exception of the last row (TOTAL FOR JOB) to remove the word "TOTAL" from each row? When I recorded the macro, I was able to remove it by the "replacement function"; however, with the last code language you provided, it now deletes all rows that do not have the "total word". Is there way around that?

Thanks again
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,964
Office Version
  1. 365
Platform
  1. Windows
Add this to the end of the code
Code:
   With Range("D2", Range("D" & Rows.Count).End(xlUp).Offset(-1))
      .Replace "Total ", "", xlPart, , False, , False, False
   End With
 

Watch MrExcel Video

Forum statistics

Threads
1,130,290
Messages
5,641,368
Members
417,206
Latest member
sam89665

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top