Fill down with visible cells not working right

[jordanburch]

Hey guys,

so i apply filters before the following code, but this code isnt working right. I just need it to apply to visible cells only and it just says calculating and only puts in in as2 which it shouldnt because thats filtered out. The rest of the rows are simply blank. When i remove all fiters and try the code it works for all 6000 lines but thats not what im wanting.

Range("as2").Formula = "=CONCATENATE(RC[-40],RC[-39])"

Range("as2:as6000").SpecialCells(xlCellTypeVisible).FillDown

Range("at2").Formula = "=SUMIF(C[-1],RC[-1],C[-9])"

Range("at2:at6000").SpecialCells(xlCellTypeVisible).FillDown

Range("aw2").Formula = "=CONCATENATE(RC[-4],RC[-3])"

Range("aw2:aw6000").SpecialCells(xlCellTypeVisible).FillDown

Any ideas is greatly appreciated!

Jordan

 

[MARK858]

What happens if you apply the formula directly?

Range("as2:as6000").SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=CONCATENATE(RC[-40],RC[-39])"
Range("at2:at6000").SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=SUMIF(C[-1],RC[-1],C[-9])"
Range("aw2:aw6000").SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=CONCATENATE(RC[-4],RC[-3])"

 

[jordanburch]

What happens if you apply the formula directly?

Range("as2:as6000").SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=CONCATENATE(RC[-40],RC[-39])"
Range("at2:at6000").SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=SUMIF(C[-1],RC[-1],C[-9])"
Range("aw2:aw6000").SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=CONCATENATE(RC[-4],RC[-3])"

works great thanks! why didnt mine work vs yours that works?

Jordan

 

[MARK858]

It is applying the formula directly to the cells and not filling down so it is a bit like the VBA equivalent of manually typing a formula in a cell rather verses manually dragging a formula down.

You could use a normal formula rather than an R1C1 formula and it would do the same for instance

Range("at2:at6000").SpecialCells(xlCellTypeVisible).Formula = "=SUMIF(AS:AS,AS2,AK:AK)"

Excel is intelligent enough to adjust for the different rows.

 

[jordanburch]

got ya thanks again!

 

[Peter_SSs]

You could use a normal formula rather than an R1C1 formula and it would do the same for instance

Range("at2:at6000").SpecialCells(xlCellTypeVisible).Formula = "=SUMIF(AS:AS,AS2,AK:AK)"

Mark, I think you will find that alternative does not work unless row 2 happens to be visible?

 

[MARK858]

@Peter_SSs I was assuming wrongly that row 2 was visible because that was the row being filled down from, after re-reading I see that is incorrect.

Thanks for pointing it out.