Delete Row Using Filters with a Macro

J

jrhguez

New Member
Joined
May 8, 2008
Messages
48
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


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

Thank you

[TABLE="width: 1501"]
<colgroup><col><col><col span="3"><col><col><col span="8"></colgroup><tbody>[TR]
[TD] Jonathan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
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

Code: 
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
 
Last edited by a moderator:
Upvote 0
You could use
Code: 
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
 
Upvote 0
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
 
Upvote 0
Replace this
Code: 
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
Code: 
   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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
How about
Code: 
   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
 
Upvote 0
That's just setting the colour to what your code had, so I've no idea why it's different.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Using ADODB way t populate data under header from file to another
Replies
0
Views
54
abdo meghari
A
WESTERNWALL
  • Question Question
Sum and Count functions
Replies
4
Views
231
WESTERNWALL
WESTERNWALL
H
  • Solved
Copy the first line and paste it at the start of all subsequent sheets in the same tab
Replies
4
Views
344
harzer
H
V
  • Question Question
Formula issues to find variations when zero is one reference value
Replies
0
Views
240
vladimiratanasiu
V
A
  • Question Question
Filtered Rows prints 7 pages when print area is set to 2
Replies
0
Views
240
angusn
A

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

Share this page

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