Using specialcells to apply a formula in a macro to only visible cells

[David]

I have an autofilter on column E which enables a user to select a specific product type to analyze. The macro should then only calculate the formula on those visible cells. Currently it seems to still attempt to calculate cells in the alphabet before the products selected. for example if the product type starts with a C, then it still attempt to calculate the formula for A and B product types when I select them to see what the macro did. The macro is below and the specialcells code is near the bottom.

Sub Rebate_Exception_v2()
'
' Rebate_Exception_v2 Macro
'

'
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1").Select
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "On Rebate Report"
Range("E1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E2").Select
ActiveCell.FormulaR1C1 = "On Rebate Report"
Range("E2").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Dim lastrow As Long

lastrow = Range("D65000").End(xlUp).Row
Selection.SpecialCells(xlCellTypeVisible).Select
Range("E2").Select
Selection.FormulaArray = _
"=INDEX('Rebate report'!R4C1:R5000C12,MATCH(1,('Rebate report'!C[-4]=RC[-3])*('Rebate report'!C[-3]=RC[-2])*('Rebate report'!C[-2]=RC[-1]),0),1)"
Selection.AutoFill Destination:=Range("E2:E" & lastrow)
'Selection.AutoFill Destination:=Range("E2:E"& lastrow)
End Sub

[#2]

Your code currently seems to be applying repetitive (although different) settings to cell E1. And it would seem also to be putting On Rebate Report into both E1 and E2, surely you only want that to be in cell E1?

There is seldom.. almost never a need to SELECT anything in VBA

Sub Rebate_Exception_v2()
'
' Rebate_Exception_v2 Macro
'

'
Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
With Range("E1")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    .Value = "On Rebate Report"
    With .Font
        .Color = -16776961
        .TintAndShade = 0
    End With
End With
Range("E2").Resize(Range("D65000").End(xlUp).Row - 1).SpecialCells(xlCellTypeVisible).FormulaArray = _
    "=INDEX('Rebate report'!R4C1:R5000C12,MATCH(1,('Rebate report'!C[-4]=RC[-3])*('Rebate report'!C[-3]=RC[-2])*('Rebate report'!C[-2]=RC[-1]),0),1)"
End Sub

LECT anything in VBA to change ranges.

[#3]

Thanks for the response Bill. After the filter is applied the data is in rows 948 to 950 and (then it hides 951 to 1208) then data displays again in row 1209 through 2113 (i.e. it is visible). I ran the macro and it did the calculation for the first 3 rows (948,949 and 950) in Column E but then it stopped. Is this because there is a break in continuous rows? What needs to change in the macro so that this does not happen and that it stops in the last row of the visible data?

[#4]

I am not really sure what is going on in your case. I just tried the below code on a whole bunch of filtered rows

range(activecell,activesheet.cells(rows.Count,activecell.Column).end(xlup)).SpecialCells(xlCellTypeVisible).formulaR1C1="=1+1"

It plopped my formula in just the filtered cells and no gaps.

Is it possible you don'r have data in D1209 through D2113, since that is the column you are using in your computation of end point for the range?