Delete Row Using Filters with a Macro

[jrhguez]

Hello: I created a macro to reformat the following spreadsheet. Column N (Resulting Current Projection) is a formula of: L + M. The formula only needs to be added to any row with code "R" from column B. The problem I am having is that even though I filter code R, the formula carries down to other rows down the bottom of the spreadsheet when I run other reports. Some reports may have 100 rows an others may have 70..All reports begin in row 6

Row #	A	B	C	D	E	F	G	H	I	J	K	L	M	N
	Job	Code	#	Acct	Category Code	Category	Original Budget	JV Change Order	Revised Budget	Commited	Other Costs	Current Projection	Requested Changes	Resulting Current Projection
6		H	2	1473225	75-00-0000	LAND
7		R	6	1473226	75-50-0050	Land	3,550,000.00	0.00	3,550,000.00	0.00	3,400,000.00	3,400,000.00		3,400,000.00
8		R	6	1473230	75-50-0150	Escrow Deposits	250,000.00	0.00	250,000.00	0.00	399,782.00	399,782.00		399,782.00
9		T	2	1473240	75-99-9990	TOTAL LAND	3,800,000.00	0.00	3,800,000.00	0.00	3,799,782.00	3,799,782.00
10		H	2	1473241	76-00-0000	TAXES & LICENSES
11		R	6	1473242	76-51-0050	Property Taxes (realty)	300,000.00	0.00	300,000.00	0.00	125,891.72	125,891.72		125,891.72
12		R	6	1473246	76-51-0250	Tax Consultant	0.00	0.00	0.00	0.00	150.00	150.00		150.00
13		T	2	1473250	76-99-9990	TOTAL TAXES & LICENSES	300,000.00	0.00	300,000.00	0.00	126,041.72	126,041.72
14		H	2	1473251	77-00-0000	LEGAL
15		R	6	1473253	77-52-0100	Construction Lender - Legal	75,000.00	0.00	75,000.00	0.00	630.00	630.00		630.00
16		R	6	1473256	77-52-0300	Legal - Other	0.00	0.00	0.00	0.00	108,751.16	108,751.16		108,751.16
17		R	6	1473258	77-52-0700	Land Use Legal/Zoning	50,000.00	0.00	50,000.00	0.00	5,770.84	5,770.84		5,770.84
18		R	6	1473259	77-52-0720	Land-Legal	150,000.00	0.00	150,000.00	0.00	156,233.58	156,233.58		156,233.58
19		R	6	1473260	77-52-0725	Loan-Legal	75,000.00	25,000.00	100,000.00	0.00	111,439.81	111,439.81		111,439.81
20		R	6	1473261	77-52-0730	Entity Formation-Legal	0.00	0.00	0.00	0.00	2,907.30	2,907.30		2,907.30
21		T	2	1473262	77-99-9990	TOTAL LEGAL	350,000.00	25,000.00	375,000.00	0.00	385,732.69	385,732.69
22		H	2	1473263	78-00-0000	CLOSING COSTS
23		R	6	1473264	78-53-0050	Title Insurance	30,000.00	33,363.00	63,363.00	0.00	105,192.12	105,192.12		105,192.12
24		R	6	1473266	78-53-0100	Recording Costs	0.00	0.00	0.00	0.00	7,762.00	7,762.00		7,762.00
25		R	6	1473268	78-53-0150	Documentary Stamps	20,000.00	0.00	20,000.00	0.00	0.00	0.00		0.00
26		R	6	1473271	78-53-0350	Closing Costs - other	200,000.00	0.00	200,000.00	0.00	7.50	7.50		7.50
27		T	2	1473276	78-99-9990	TOTAL CLOSING COSTS	250,000.00	33,363.00	283,363.00	0.00	112,961.62	112,961.62
28		H	2	1473277	79-00-0000	FINANCING
29		R	6	1473278	79-54-0150	Construction Loan Fees	246,000.00	15,000.00	261,000.00	0.00	244,470.00	244,470.00		244,470.00
30		R	6	1473279	79-54-0250	Equity Placement Fee	400,000.00	0.00	400,000.00	0.00	340,000.00	340,000.00		340,000.00
31		R	6	1473283	79-54-0500	Financing Fee - Other	207,433.00	0.00	207,433.00	0.00	23.00	23.00		23.00
32		R	6	1473285	79-54-0600	Appraisal	100,000.00	10,000.00	110,000.00	0.00	9,474.00	9,474.00		9,474.00
33		T	2	1473286	79-99-9990	TOTAL FINANCING	953,433.00	25,000.00	978,433.00	0.00	593,967.00	593,967.00
34		H	2	1473287	80-00-0000	MUNI FEES
35		R	6	1473288	80-55-0050	Planning Fees	10,000.00	0.00	10,000.00	0.00	0.00	0.00		0.00
36		R	6	1473289	80-55-0100	Building Fees	282,000.00	0.00	282,000.00	0.00	294,809.00	294,809.00		294,809.00
37		R	6	1473291	80-55-0200	Park Fees	17,000.00	0.00	17,000.00	0.00	0.00	0.00		0.00
38		R	6	1473293	80-55-0300	Traffic Fees	60,000.00	0.00	60,000.00	0.00	0.00	0.00		0.00
39		R	6	1473295	80-55-0400	Engineering Fees	26,050.00	0.00	26,050.00	0.00	0.00	0.00		0.00
40		R	6	1473296	80-55-0460	Drainage Impact Fees	132,000.00	0.00	132,000.00	0.00	121,182.00	121,182.00		121,182.00
41		R	6	1473297	80-55-0480	Utility Fees	0.00	0.00	0.00	0.00	(92.00)	(92.00)		(92.00)
42		R	6	1473299	80-55-0550	Other Muni Fees	585,075.00	0.00	585,075.00	0.00	648,711.00	648,711.00		648,711.00
43		R	6	1473300	80-55-0600	Waterline SDC	24,000.00	0.00	24,000.00	0.00	0.00	0.00		0.00
44		R	6	1473302	80-55-0800	Performance Bonds	0.00	0.00	0.00	0.00	2,644.00	2,644.00		2,644.00
45		R	6	1473303	80-55-1450	Permit Processing	20,000.00	0.00	20,000.00	0.00	19,563.00	19,563.00		19,563.00
		T	2	1473311	80-99-9990	TOTAL MUNI FEES	1,156,125.00	0.00	1,156,125.00	0.00	1,086,817.00	1,086,817.00

<colgroup><col><col><col span="3"><col><col><col span="8"></colgroup><tbody>
</tbody>

Thank you

Jonathan

<colgroup><col><col><col span="3"><col><col><col span="8"></colgroup><tbody>
</tbody>

 

[jrhguez]

Perhaps this will help..The same question applies when i filter the accounts highlighted in red below. These accounts need to be deleted, however they can be in different row #s within the report

Sub Macro_Worksheet()
'
' Macro_Worksheet Macro
'


'
    Windows("wip_projcost_pcmain_1228(1).xlsx").Activate
    Range("A4:T4").Select
    Selection.AutoFilter
    Columns("M:M").Select
    Selection.Delete Shift:=xlToLeft
    Columns("O:O").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:Q").Select
    Selection.ColumnWidth = 15
    Columns("R:R").Select
    Selection.ColumnWidth = 15
    Columns("M:N").Select
    Selection.Insert Shift:=xlToRight
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "DATA THIS"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "COLUMN"
    Range("M1:M2").Select
    Range("M2").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    Range("M4").Select
    ActiveCell.FormulaR1C1 = "Requested Changes"
    Range("N4").Select
    ActiveCell.FormulaR1C1 = "Resulting Current Projection"
    Range("G5").Select
    ActiveWindow.SmallScroll Down:=-3
    Range("F5:S381").Select
    Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    Columns("A:E").Select
    Selection.EntireColumn.Hidden = False
    Range("D11").Select
        [COLOR=#ff0000]ActiveSheet.Range("$A$4:$T$120").AutoFilter Field:=4, Criteria1:=Array( _
        "1472475", "1472476", "1473539", "1473542"), Operator:=xlFilterValues[/COLOR]
    Rows("5:227").Select
    Range("B5").Activate
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$4:$T$162").AutoFilter Field:=4
    Range("B9").Select
    ActiveSheet.Range("$A$4:$T$162").AutoFilter Field:=1, Criteria1:="<>"
    Rows("162:162").Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$4:$T$161").AutoFilter Field:=1
    ActiveSheet.Range("$A$4:$T$161").AutoFilter Field:=2, Criteria1:="R"
    Range("N6").Select
    ActiveCell.FormulaR1C1 = "=+RC[-2]+RC[-1]"
    Range("N6").Select
    Selection.Copy
    Range("N7:N159").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("M6:N159").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("M10").Select
    ActiveSheet.Range("$A$4:$T$161").AutoFilter Field:=2
    Range("E10").Select
    ActiveWindow.SmallScroll Down:=-18
    Columns("B:D").Select
    Selection.EntireColumn.Hidden = True
    Range("G5").Select
    ActiveWindow.SmallScroll Down:=-6
    Range("H5").Select
    ActiveWindow.FreezePanes = True
    ActiveWindow.SmallScroll Down:=-18
    Range("A4").Select
    ActiveWindow.SmallScroll ToRight:=2
    Range("A4:T4").Select
    Selection.AutoFilter
    Sheets("Projected_Cost_Report").Select
    Sheets("Projected_Cost_Report").Name = "PCR Worksheet"
    Range("F7").Select
    Windows("PERSONAL.XLSB").Activate
End Sub

 

[Fluff]

You could use

With ActiveSheet
   .Range("$A$4:$T$120").AutoFilter Field:=4, Criteria1:=Array( _
        "1472475", "1472476", "1473539", "1473542"), Operator:=xlFilterValues
   .AutoFilter.Range.Offset(1).EntireRow.Delete
End With

 

[jrhguez]

Thank you...Any suggestions for my first question?

Hello: I created a macro to reformat the following spreadsheet. Column N (Resulting Current Projection) is a formula of: L + M. The formula only needs to be added to any row with code "R" from column B. The problem I am having is that even though I filter code R, the formula carries down to other rows down the bottom of the spreadsheet when I run other reports. Some reports may have 100 rows an others may have 70..All reports begin in row 6

 

[Fluff]

Replace this

ActiveSheet.Range("$A$4:$T$161").AutoFilter Field:=1
    ActiveSheet.Range("$A$4:$T$161").AutoFilter Field:=2, Criteria1:="R"
    Range("N6").Select
    ActiveCell.FormulaR1C1 = "=+RC[-2]+RC[-1]"
    Range("N6").Select
    Selection.Copy
    Range("N7:N159").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

with

   With ActiveSheet
      .Range("$A$4:$T$161").AutoFilter Field:=1
      .Range("$A$4:$T$161").AutoFilter Field:=2, Criteria1:="R"
      .AutoFilter.Range.Offset(1).Columns(14).FormulaR1C1 = "=RC[-2]+RC[-1]"
   End With

 

[jrhguez]

So sorry, how could I apply the same so the tintandshade doesn't copy down like the formula was doing it

Range("M6:N159").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0

Thanks

 

[jrhguez]

Good Morning: I was wondering if you got a chance to look to my last minute question from yesterday (#6)...Thank you again for all your assistance

Regards,
Jonathan

 

[Fluff]

How about

   With ActiveSheet
      .Range("$A$4:$T$161").AutoFilter Field:=1
      .Range("$A$4:$T$161").AutoFilter Field:=2, Criteria1:="R"
      .AutoFilter.Range.Offset(1).Columns(14).FormulaR1C1 = "=RC[-2]+RC[-1]"
      With .AutoFilter.Range.Offset(1).Columns("M:N").Interior
         .Pattern = xlSolid
         .PatternColorIndex = xlAutomatic
         .ThemeColor = xlThemeColorAccent5
         .TintAndShade = 0.599993896298105
         .PatternTintAndShade = 0
      End With
   End With

 

[jrhguez]

Hi again: This worked, but now is not showing the column in blue like it was showing it before

 

[Fluff]

That's just setting the colour to what your code had, so I've no idea why it's different.