Forum Discussion

David's avatar
David
Copper Contributor
Sep 27, 2017

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

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 stil...
Excel Desktop
Macros & VBA
Bill Benson's avatar
Bill Benson
Copper Contributor
Sep 27, 2017

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.

 

  • David's avatar
    David
    Copper Contributor
    Sep 28, 2017

    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?

    • Bill Benson's avatar
      Bill Benson
      Copper Contributor
      Sep 28, 2017

      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?

Resources