Forum Discussion

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

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

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?