Delete Rows using a Macro

jrhguez · Jun 12, 2019

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

Category	Original Budget	JV Change Order	Revised Budget	Commited	Other Costs	Current Projection	Cost Incurred to Date	Balance Complete	Variance Current Month	Variance Project to Date	Cost Per Unit	Cost Per Sq. Ft.	% Complete
GENERAL CONDITIONS
Construction Payroll	1,925,000.00	277,052.00	2,202,052.00	0.00	2,892,778.57	2,892,778.57	2,878,247.37	14,531.20	(35,000.00)	690,726.57	9,242.10	6.41	99.50
Construction Travel	20,000.00	0.00	20,000.00	0.00	43,943.86	43,943.86	43,943.86	0.00	0.00	23,943.86	140.40	0.10	100.00
Relocation Moving Expenses	0.00	0.00	0.00	0.00	5,651.31	5,651.31	5,651.31	0.00	0.00	5,651.31	18.06	0.01	100.00
Recruiting Costs	60,000.00	0.00	60,000.00	0.00	104,662.74	104,662.74	104,662.74	0.00	0.00	44,662.74	334.39	0.23	100.00
Office Supplies	19,200.00	0.00	19,200.00	0.00	23,680.82	23,680.82	23,680.82	0.00	0.00	4,480.82	75.66	0.05	100.00
Postage & Delivery	8,400.00	0.00	8,400.00	0.00	4,776.92	4,776.92	4,776.92	0.00	0.00	(3,623.08)	15.26	0.01	100.00
Cell Phones	21,600.00	0.00	21,600.00	0.00	22,147.86	22,147.86	22,147.86	0.00	0.00	547.86	70.76	0.05	100.00
Relations	24,000.00	0.00	24,000.00	0.00	7,782.69	7,782.69	7,782.69	0.00	0.00	(16,217.31)	24.86	0.02	100.00
Legal Fees	20,000.00	0.00	20,000.00	0.00	147,818.60	147,818.60	147,818.60	0.00	0.00	127,818.60	472.26	0.33	100.00
Plans & Printing	30,000.00	8,000.00	38,000.00	0.00	33,090.58	33,090.58	33,090.58	0.00	0.00	(4,909.42)	105.72	0.07	100.00
Office Trailers & Storage	62,700.00	0.00	62,700.00	586.22	39,705.76	40,291.98	40,291.98	0.00	0.00	(22,408.02)	128.73	0.09	100.00
Office Equipment	35,000.00	0.00	35,000.00	0.00	61,890.91	61,890.91	61,890.91	0.00	0.00	26,890.91	197.73	0.14	100.00
Office Electricity	8,400.00	0.00	8,400.00	0.00	11,916.71	11,916.71	11,916.71	0.00	0.00	3,516.71	38.07	0.03	100.00
Office Water	8,776.00	0.00	8,776.00	0.00	9,810.08	9,810.08	9,810.08	0.00	0.00	1,034.08	31.34	0.02	100.00
TOTAL GENERAL CONDITIONS	2,243,076.00	285,052.00	2,528,128.00	586.22	3,409,657.41	3,410,243.63	3,395,712.43	14,531.20	(35,000.00)	882,115.63	10,895.35	7.55	99.57
SITE WORK
Miscellaneous Site Work	70,000.00	0.00	70,000.00	63,392.39	7,701.77	71,094.16	71,094.16	0.00	0.00	1,094.16	227.14	0.16	100.00
Street Lights	0.00	0.00	0.00	0.00	66,500.18	66,500.18	66,500.18	0.00	0.00	66,500.18	212.46	0.15	100.00
Earthwork	1,682,348.00	161,493.61	1,843,841.61	2,113,768.72	250.00	2,114,018.72	2,114,018.72	0.00	0.00	270,177.11	6,754.05	4.68	100.00
Shoring & Underpinning	25,000.00	0.00	25,000.00	50,572.16	0.00	50,572.16	50,572.16	0.00	0.00	25,572.16	161.57	0.11	100.00
Piling	152,274.00	0.00	152,274.00	119,832.00	0.00	119,832.00	119,832.00	0.00	0.00	(32,442.00)	382.85	0.27	100.00
Asphalt Paving	191,931.00	0.00	191,931.00	248,651.00	102.37	248,753.37	248,753.37	0.00	0.00	56,822.37	794.74	0.55	100.00
Utilities Relocation	300,000.00	0.00	300,000.00	0.00	353,465.82	353,465.82	353,465.82	0.00	0.00	53,465.82	1,129.28	0.78	100.00
TOTAL SITE WORK	2,421,553.00	161,493.61	2,583,046.61	2,596,216.27	428,020.14	3,024,236.41	3,024,236.41	0.00	0.00	441,189.80	9,662.10	6.70	100.00
LANDSCAPING
Miscellaneous Site Work	1,288,740.00	156,461.50	1,445,201.50	1,499,332.45	5,579.86	1,504,912.31	1,504,912.31	0.00	0.00	59,710.81	4,808.03	3.33	100.00
Pool & Spa	114,300.00	0.00	114,300.00	121,300.00	0.00	121,300.00	121,300.00	0.00	0.00	7,000.00	387.54	0.27	100.00
Landscaping	200,000.00	166,215.22	366,215.22	394,460.22	4,992.00	399,452.22	399,452.22	0.00	0.00	33,237.00	1,276.21	0.88	100.00
TOTAL LANDSCAPING	1,603,040.00	322,676.72	1,925,716.72	2,015,092.67	10,571.86	2,025,664.53	2,025,664.53	0.00	0.00	99,947.81	6,471.77	4.49	100.00
APARTMENT STRUCTURES
Final Cleaning	83,102.00	3,500.00	86,602.00	127,873.00	13,575.71	141,448.71	141,448.71	0.00	0.00	54,846.71	451.91	0.31	100.00
Cast in Place Concrete	994,841.00	0.00	994,841.00	1,321,353.20	79,064.15	1,400,417.35	1,400,417.35	0.00	0.00	405,576.35	4,474.18	3.10	100.00
PT & Reinforcing Steel	372,461.00	3,600.00	376,061.00	373,395.24	48,385.74	421,780.98	421,780.98	0.00	0.00	45,719.98	1,347.54	0.93	100.00
Cast in Place Concrte-Material	1,105,171.00	4,000.00	1,109,171.00	1,406,719.35	24,416.28	1,431,135.63	1,431,135.63	0.00	0.00	321,964.63	4,572.32	3.17	100.00
Post Tensioned Cable Mat	238,700.00	0.00	238,700.00	141,985.91	0.00	141,985.91	141,985.91	0.00	0.00	(96,714.09)	453.63	0.31	100.00
Formwork Cast in Plac Con	1,180,746.00	0.00	1,180,746.00	1,188,195.68	1,142.76	1,189,338.44	1,189,338.44	0.00	0.00	8,592.44	3,799.80	2.63	100.00
Place and Finish Labor	38,033.00	0.00	38,033.00	38,033.00	0.00	38,033.00	38,033.00	0.00	0.00	0.00	121.51	0.08	100.00
Concrete Pump	565,165.00	0.00	565,165.00	595,376.57	161,622.12	756,998.69	756,998.69	0.00	0.00	191,833.69	2,418.53	1.68	100.00
PT & Reinforc Steel Instal	275,735.00	0.00	275,735.00	285,635.00	1,203.75	286,838.75	286,838.75	0.00	0.00	11,103.75	916.42	0.64	100.00
Gypsum Cement Underlayment	589,012.00	0.00	589,012.00	748,928.00	0.00	748,928.00	748,928.00	0.00	0.00	159,916.00	2,392.74	1.66	100.00
Concrete Fill Materials	0.00	0.00	0.00	0.00	222,797.09	222,797.09	56,476.09	166,321.00	166,321.00	222,797.09	711.81	0.49	25.35
Brick Masonry	0.00	0.00	0.00	0.00	314,008.00	314,008.00	322,472.52	(8,464.52)	60,500.00	314,008.00	1,003.22	0.70	102.70
Concrete Unit Masonry	1,448,718.00	90,918.00	1,539,636.00	1,591,184.37	36.18	1,591,220.55	1,591,184.37	36.18	0.00	51,584.55	5,083.77	3.52	100.00
Metal Railings	495,668.00	0.00	495,668.00	395,510.68	0.00	395,510.68	395,510.68	0.00	0.00	(100,157.32)	1,263.61	0.88	100.00
Misc. Metals	854,842.00	119,535.00	974,377.00	720,757.87	247,005.74	967,763.61	967,763.61	0.00	0.00	(6,613.39)	3,091.90	2.14	100.00
Rough Carpentry	5,511,701.00	122,942.49	5,634,643.49	5,153,875.01	482,823.30	5,636,698.31	5,636,698.31	0.00	0.00	2,054.82	18,008.62	12.48	100.00
Interior Finish Carpentry	181,606.00	24,398.00	206,004.00	863,760.96	0.00	863,760.96	863,760.96	0.00	0.00	657,756.96	2,759.62	1.91	100.00
Waterproofing & Dampproofing	1,368,226.00	800.00	1,369,026.00	1,138,583.35	28,162.82	1,166,746.17	1,166,746.17	0.00	0.00	(202,279.83)	3,727.62	2.58	100.00
Preventative Coating/Remediati	77,624.00	0.00	77,624.00	93,662.00	0.00	93,662.00	93,662.00	0.00	0.00	16,038.00	299.24	0.21	100.00
Insulation	524,728.00	0.00	524,728.00	703,255.00	1,180.51	704,435.51	704,435.51	0.00	0.00	179,707.51	2,250.59	1.56	100.00
Roofing	879,281.00	38,458.00	917,739.00	545,506.00	17,100.00	562,606.00	562,606.00	0.00	0.00	(355,133.00)	1,797.46	1.25	100.00
Siding	1,136,736.00	16,250.00	1,152,986.00	968,209.05	5,929.83	974,138.88	974,138.88	0.00	0.00	(178,847.12)	3,112.26	2.16	100.00
Fire stopping	45,000.00	0.00	45,000.00	0.00	7,579.59	7,579.59	7,579.59	0.00	0.00	(37,420.41)	24.22	0.02	100.00
Overhead Coiling Doors	61,896.00	83,148.00	145,044.00	159,491.00	497.25	159,988.25	159,988.25	0.00	0.00	14,944.25	511.14	0.35	100.00
Entrance & Storefront	347,553.00	1,800.00	349,353.00	469,927.28	1,563.34	471,490.62	471,490.62	0.00	0.00	122,137.62	1,506.36	1.04	100.00
Windows	673,323.00	0.00	673,323.00	968,699.73	0.00	968,699.73	968,699.73	0.00	0.00	295,376.73	3,094.89	2.15	100.00
Door Hardware	1,142,765.00	6,260.00	1,149,025.00	775,713.54	817.20	776,530.74	776,530.74	0.00	0.00	(372,494.26)	2,480.93	1.72	100.00
Gypsum Board	2,622,960.00	160,942.00	2,783,902.00	3,134,502.00	888.94	3,135,390.94	3,135,390.94	0.00	0.00	351,488.94	10,017.22	6.94	100.00
Tile/Stone	1,331,925.00	158,440.00	1,490,365.00	1,468,505.00	1,219.69	1,469,724.69	1,469,724.69	0.00	0.00	(20,640.31)	4,695.61	3.26	100.00
Painting	755,456.00	31,500.00	786,956.00	1,112,475.11	0.00	1,112,475.11	1,112,475.11	0.00	0.00	325,519.11	3,554.23	2.46	100.00
Signage	45,000.00	0.00	45,000.00	0.00	17,971.00	17,971.00	17,971.00	0.00	0.00	(27,029.00)	57.42	0.04	100.00
Toilet/Bath Accessories	241,843.00	26,530.00	268,373.00	131,851.00	269.66	132,120.66	132,120.66	0.00	0.00	(136,252.34)	422.11	0.29	100.00
Fireplaces & Accessories	35,000.00	0.00	35,000.00	0.00	68,745.00	68,745.00	68,745.00	0.00	0.00	33,745.00	219.63	0.15	100.00
Storage Units	54,000.00	0.00	54,000.00	0.00	0.00	0.00	0.00	0.00	0.00	(54,000.00)	0.00	0.00	0.00
Postal Specialties	72,000.00	0.00	72,000.00	43,370.00	0.00	43,370.00	43,370.00	0.00	0.00	(28,630.00)	138.56	0.10	100.00
Misc. Specialties	10,000.00	3,500.00	13,500.00	0.00	13,393.59	13,393.59	13,393.59	0.00	0.00	(106.41)	42.79	0.03	100.00
Residential Appliances	837,531.00	0.00	837,531.00	914,138.05	24,682.82	938,820.87	938,820.87	0.00	0.00	101,289.87	2,999.43	2.08	100.00
Window Treatments	150,723.00	0.00	150,723.00	110,200.00	0.00	110,200.00	110,200.00	0.00	0.00	(40,523.00)	352.08	0.24	100.00
Residential Casework	772,090.00	37,488.00	809,578.00	782,136.00	0.00	782,136.00	782,136.00	0.00	0.00	(27,442.00)	2,498.84	1.73	100.00
Stone/Tile Countertops	305,575.00	11,502.00	317,077.00	386,586.00	0.00	386,586.00	386,586.00	0.00	0.00	69,509.00	1,235.10	0.86	100.00
Elevators	902,746.00	3,984.00	906,730.00	1,060,554.08	8,532.86	1,069,086.94	1,069,086.94	0.00	0.00	162,356.94	3,415.61	2.37	100.00
Trash Chutes	101,602.00	1,905.00	103,507.00	109,641.00	0.00	109,641.00	109,641.00	0.00	0.00	6,134.00	350.29	0.24	100.00
Fire Sprinkler System	780,698.00	28,000.00	808,698.00	879,000.00	2,805.49	881,805.49	881,805.49	0.00	0.00	73,107.49	2,817.27	1.95	100.00
Water Submetering	42,255.00	0.00	42,255.00	35,967.00	0.00	35,967.00	35,967.00	0.00	0.00	(6,288.00)	114.91	0.08	100.00
Plumbing System	2,474,823.00	47,278.63	2,522,101.63	2,471,859.48	69,025.64	2,540,885.12	2,540,885.12	0.00	0.00	18,783.49	8,117.84	5.63	100.00
HVAC System	1,863,910.00	10,018.00	1,873,928.00	1,829,186.06	19,420.69	1,848,606.75	1,848,606.75	0.00	0.00	(25,321.25)	5,906.09	4.09	100.00
Electrical	4,103,361.00	637,001.47	4,740,362.47	5,660,906.65	4,841.49	5,665,748.14	5,665,748.14	0.00	0.00	925,385.67	18,101.43	12.55	100.00
Electrical Switch Gear	0.00	46,495.00	46,495.00	0.00	0.00	0.00	0.00	0.00	0.00	(46,495.00)	0.00	0.00	0.00
Access Control System	151,816.00	0.00	151,816.00	0.00	0.00	0.00	0.00	0.00	0.00	(151,816.00)	0.00	0.00	0.00
TOTAL APARTMENT STRUCTURES	37,847,947.00	1,720,193.59	39,568,140.59	40,906,508.22	1,890,708.23	42,797,216.45	42,639,323.79	157,892.66	226,821.00	3,229,075.86	136,732.32	94.79	99.63
LEASING OFFICE/CLUBHOUSE
Architectural Woodwork	247,640.00	0.00	247,640.00	278,608.85	13,556.76	292,165.61	292,165.61	0.00	0.00	44,525.61	933.44	0.65	100.00
TOTAL LEASING OFFICE/CLUBHOUSE	247,640.00	0.00	247,640.00	278,608.85	13,556.76	292,165.61	292,165.61	0.00	0.00	44,525.61	933.44	0.65	100.00
GARAGE/PARKING STRUCTURE
Precast Structural Concrete	2,907,500.00	0.00	2,907,500.00	2,872,150.00	0.00	2,872,150.00	2,872,150.00	0.00	0.00	(35,350.00)	9,176.20	6.36	100.00
TOTAL GARAGE/PARKING STRUCTURE	2,907,500.00	0.00	2,907,500.00	2,872,150.00	0.00	2,872,150.00	2,872,150.00	0.00	0.00	(35,350.00)	9,176.20	6.36	100.00
STAGING & LOGISTICS
Punch Carpenter	109,550.00	0.00	109,550.00	0.00	159,930.99	159,930.99	159,930.99	0.00	0.00	50,380.99	510.96	0.35	100.00
General Labor	82,548.00	2,040.00	84,588.00	0.00	481,074.57	481,074.57	481,074.57	0.00	0.00	396,486.57	1,536.98	1.07	100.00
Backcharge/Damage & Correction	0.00	0.00	0.00	0.00	3,845.20	3,845.20	3,845.20	0.00	0.00	3,845.20	12.28	0.01	100.00
Technology Fees & Licenses	30,000.00	0.00	30,000.00	0.00	28,113.17	28,113.17	28,113.17	0.00	0.00	(1,886.83)	89.82	0.06	100.00
Professional Scheduling	0.00	0.00	0.00	0.00	112,940.00	112,940.00	112,940.00	0.00	0.00	112,940.00	360.83	0.25	100.00
Safety Inspections	24,000.00	0.00	24,000.00	0.00	13,425.26	13,425.26	13,425.26	0.00	0.00	(10,574.74)	42.89	0.03	100.00
Temporary Electric	70,000.00	58,087.34	128,087.34	0.00	336,331.03	336,331.03	336,331.03	0.00	0.00	208,243.69	1,074.54	0.74	100.00
Temp HVAC	50,000.00	0.00	50,000.00	0.00	1,436.07	1,436.07	1,436.07	0.00	0.00	(48,563.93)	4.59	0.00	100.00
Temp Storage/Trailer	2,500.00	0.00	2,500.00	0.00	0.00	0.00	0.00	0.00	0.00	(2,500.00)	0.00	0.00	0.00
Sanitary Facilities	55,000.00	0.00	55,000.00	0.00	74,754.14	74,754.14	74,754.14	0.00	0.00	19,754.14	238.83	0.17	100.00
Misc. Tools & Materials	15,000.00	0.00	15,000.00	0.00	172,162.24	172,162.24	172,162.24	0.00	0.00	157,162.24	550.04	0.38	100.00
Equipment Owned	10,000.00	0.00	10,000.00	0.00	0.00	0.00	0.00	0.00	0.00	(10,000.00)	0.00	0.00	0.00
Equipment Rented	60,000.00	50,433.00	110,433.00	103,256.98	456,892.34	560,149.32	560,149.32	0.00	0.00	449,716.32	1,789.61	1.24	100.00
Construction Trash Chutes	12,000.00	0.00	12,000.00	0.00	6,433.01	6,433.01	6,433.01	0.00	0.00	(5,566.99)	20.55	0.01	100.00
Safety Equipment & Services	12,000.00	0.00	12,000.00	0.00	42,974.24	42,974.24	42,974.24	0.00	0.00	30,974.24	137.30	0.10	100.00
Temporary Fencing	25,000.00	0.00	25,000.00	0.00	44,999.50	44,999.50	44,999.50	0.00	0.00	19,999.50	143.77	0.10	100.00
Temp Environmental Controls	30,000.00	0.00	30,000.00	0.00	76,285.46	76,285.46	76,285.46	0.00	0.00	46,285.46	243.72	0.17	100.00
Temporary Project Signage	4,500.00	0.00	4,500.00	0.00	1,900.51	1,900.51	1,900.51	0.00	0.00	(2,599.49)	6.07	0.00	100.00
Progress Cleaning	10,000.00	0.00	10,000.00	0.00	5,128.40	5,128.40	5,128.40	0.00	0.00	(4,871.60)	16.38	0.01	100.00
Construction Waste/Disposal	234,750.00	980.00	235,730.00	0.00	298,106.05	298,106.05	298,106.05	0.00	0.00	62,376.05	952.42	0.66	100.00
Police Detail/Traffic Control	57,000.00	0.00	57,000.00	0.00	421,922.87	421,922.87	421,922.87	0.00	0.00	364,922.87	1,348.00	0.93	100.00
Surveying	30,000.00	2,200.00	32,200.00	89,835.00	9,987.87	99,822.87	99,822.87	0.00	0.00	67,622.87	318.92	0.22	100.00
Special Inspections & Testing	50,000.00	0.00	50,000.00	144,085.88	21,926.41	166,012.29	166,012.29	0.00	0.00	116,012.29	530.39	0.37	100.00
TOTAL STAGING & LOGISTICS	973,848.00	113,740.34	1,087,588.34	337,177.86	2,770,569.33	3,107,747.19	3,107,747.19	0.00	0.00	2,020,158.85	9,928.90	6.88	100.00
WARRANTY
Warranty	91,800.00	0.00	91,800.00	0.00	51,000.00	51,000.00	42,135.46	8,864.54	0.00	(40,800.00)	162.94	0.11	82.62
TOTAL WARRANTY	91,800.00	0.00	91,800.00	0.00	51,000.00	51,000.00	42,135.46	8,864.54	0.00	(40,800.00)	162.94	0.11	82.62
CONSTRUCTION INSURANCE
Builders Risk Insurance	303,238.00	0.00	303,238.00	0.00	343,489.40	343,489.40	343,489.40	0.00	0.00	40,251.40	1,097.41	0.76	100.00
General Liability	327,304.00	0.00	327,304.00	0.00	350,966.14	350,966.14	350,966.14	0.00	0.00	23,662.14	1,121.30	0.78	100.00
TOTAL CONSTRUCTION INSURANCE	630,542.00	0.00	630,542.00	0.00	694,455.54	694,455.54	694,455.54	0.00	0.00	63,913.54	2,218.71	1.54	100.00
CONSTRUCTION FEES
Construction Fees	2,127,143.00	93,057.32	2,220,200.32	0.00	2,220,200.62	2,220,200.62	2,220,200.32	0.30	0.00	0.30	7,093.29	4.92	100.00
TOTAL CONSTRUCTION FEES	2,127,143.00	93,057.32	2,220,200.32	0.00	2,220,200.62	2,220,200.62	2,220,200.32	0.30	0.00	0.30	7,093.29	4.92	100.00
CONTINGENCY
Construction Contingency	1,959,361.00	31,251.11	1,990,612.11	0.00	0.00	0.00	0.00	0.00	0.00	(1,990,612.11)	0.00	0.00	0.00
TOTAL CONTINGENCY	1,959,361.00	31,251.11	1,990,612.11	0.00	0.00	0.00	0.00	0.00	0.00	(1,990,612.11)	0.00	0.00	0.00
Rebates	(203,450.00)	0.00	(203,450.00)	0.00	(216,070.00)	(216,070.00)	(216,070.00)	0.00	0.00	(12,620.00)	(690.32)	(0.48)	100.00
Reimbursements	0.00	0.00	0.00	139,460.41	(375,830.68)	(236,370.27)	(236,370.27)	0.00	0.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.00	0.00	(248,990.27)	(1,445.50)	(1.00)	100.00
TOTAL FOR JOB 350011	52,850,000.00	2,727,464.69	55,577,464.69	49,145,800.50	10,896,839.21	60,042,639.71	59,861,351.01	181,288.70	191,821.00	4,465,175.02	191,829.52	132.98	99.70

Fluff · Jun 12, 2019

Assuming all the figures are values, rather than formulae, how about

Code:

Sub jrhguez()
   With Range("B3", Range("B" & Rows.Count).End(xlUp))
      .SpecialCells(xlConstants).Offset(-1).EntireRow.Delete
   End With
End Sub

jrhguez · Jun 12, 2019

I have started a macro because there are other formatting I am trying work with. I tried to copy your rule in my macro, but it gives me error messages.. This is my macro and tried to add your rule at the end..By the way, the "totals" are in column D, not on B...thanks again

Code:

Sub Macro4()
'
' Macro4 Macro
'


'
    Range("E2:H2").Select
    Selection.ClearContents
    Range("D3").Select
    Selection.ClearContents
End Sub
Sub PCR_One_Page()
'
' PCR_One_Page Macro
'


'
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft
    Rows("1:4").Select
    Range("D4").Activate
    Selection.RowHeight = 35
    Range("E2:H2").Select
    Selection.ClearContents
    Range("D3").Select
    Selection.ClearContents
    Range("E3").Select
    Selection.ClearContents
    Range("G3:H3").Select
    Selection.Cut
    Range("D3").Select
    ActiveSheet.Paste
    Range("D1:F3").Select
    With Selection.Font
        .Name = "Tahoma"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("D3").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("D4:Q4").Select
    With Selection.Font
        .Name = "Tahoma"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -4.99893185216834E-02
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = False
    Range("E5:Q134").Select
    Selection.NumberFormat = "#,#[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0_"]#0_[/URL] );(#,##0)"
    Columns("E:Q").Select
    Selection.ColumnWidth = 15
    Columns("D:D").Select
    Selection.ColumnWidth = 35
    Range("F3").Select
    ActiveWindow.SmallScroll Down:=-147
End Sub
[B]sub[/B]
[B]With Range("d5", Range("d" & Rows.Count).End(xlUp))[/B]
[B]      .SpecialCells(xlConstants).Offset(-1).EntireRow.Delete[/B]
[B]   End With[/B]
[B]End Sub[/B]

Fluff · Jun 12, 2019

Which column is "Category" in?

jrhguez · Jun 12, 2019

Originally is on F, but the original file we need to delete: Column A (Job), Column E (Category Code) and Column M(Cost for Current Month)..After deleting those 3 columns (which I recorded in the macro I sent you); "category" ends up on column D. I could send you the report it self if you are not clear

Fluff · Jun 12, 2019

In that case try

Code:

Sub jrhguez()
   With Range("E3", Range("E" & Rows.Count).End(xlUp))
      .SpecialCells(xlConstants).Offset(-1).EntireRow.Delete
   End With
End Sub

jrhguez · Jun 12, 2019

Do i just copy what you wrote to the bottom of the macro? Do i need to include "sub jrhguez()? The reason I am asking, is bc I am getting the following message:
"Compile error: only comments may appear after end sub, end function or End property"

Fluff · Jun 12, 2019

Firstly just run it on it's own (after running your code) to see if it does what you need

jrhguez · Jun 12, 2019

it looks like it worked..so how could i incorporate that into my existing macro, the one I sent you..thank you

Fluff · Jun 12, 2019

Simply add this

Code:

   With Range("E3", Range("E" & Rows.Count).End(xlUp))
      .SpecialCells(xlConstants).Offset(-1).EntireRow.Delete
   End With

just before the End Sub line of your code

Delete Rows using a Macro

jrhguez

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

jrhguez

New Member

Fluff

MrExcel MVP, Moderator

jrhguez

New Member

Fluff

MrExcel MVP, Moderator

jrhguez

New Member

Fluff

MrExcel MVP, Moderator

jrhguez

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Delete Rows using a Macro

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock