Speed Up VBA code

[dlatha01]

Hi guys,

any help would be appreciated to speed up the below code.

I guess it would be around the countifs ?

Many Thanks
David

'If July HC 2014 is selected
Application.ScreenUpdating = False
Sheets("Headcount").Range("B6:H19").ClearContents
Range("K6:Q19").ClearContents
Range("B25:H42").ClearContents
Range("K25:Q42").ClearContents
Range("A1").Select
If Range("AB16") = 1 Then
Range("B6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-1],rank,R5C2,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-1],rank,R5C2,gender,R3C2,JulyHC,""Y""))"
Range("B6").Copy Range("b6:B19")
Range("C6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-2],rank,R5C3,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-2],rank,R5C3,gender,R3C2,JulyHC,""Y""))"
Range("C6").Copy Range("c6:c19")
Range("D6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-3],rank,R5C4,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-3],rank,R5C4,gender,R3C2,JulyHC,""Y""))"
Range("D6").Copy Range("d6:d19")
Range("E6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-4],rank,R5C5,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-4],rank,R5C5,gender,R3C2,JulyHC,""Y""))"
Range("E6").Copy Range("e6:E19")
Range("F6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-5],rank,R5C6,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-5],rank,R5C6,gender,R3C2,JulyHC,""Y""))"
Range("F6").Copy Range("f6:f19")
Range("G6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-6],rank,R5C7,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-6],rank,R5C7,gender,R3C2,JulyHC,""Y""))"
Range("G6").Copy Range("g6:g19")
Range("H6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-7],rank,R5C8,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-7],rank,R5C8,gender,R3C2,gender,R3C2,JulyHC,""Y""))"
Range("H6").Copy Range("h6:h19")
Range("K6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-1],rank,R5C11,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-1],rank,R5C11,gender,R3C11,JulyHC,""Y""))"
Range("K6").Copy Range("k6:k19")
Range("L6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-2],rank,R5C12,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-2],rank,R5C12,gender,R3C11,JulyHC,""Y""))"
Range("L6").Copy Range("l6:l19")
Range("m6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-3],rank,R5C13,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-3],rank,R5C13,gender,R3C11,JulyHC,""Y""))"
Range("m6").Copy Range("m6:m19")
Range("n6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-4],rank,R5C14,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-4],rank,R5C14,gender,R3C11,JulyHC,""Y""))"
Range("n6").Copy Range("n6:n19")
Range("o6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-5],rank,R5C15,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-5],rank,R5C15,gender,R3C11,JulyHC,""Y""))"
Range("o6").Copy Range("o619")
Range("p6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-6],rank,R5C16,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-6],rank,R5C16,gender,R3C11,JulyHC,""Y""))"
Range("p6").Copy Range("p619")
Range("q6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-7],rank,R5C17,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-7],rank,R5C17,gender,R3C11,JulyHC,""Y""))"
Range("q6").Copy Range("q6:q19")
Range("B25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-1],rank,R24C2,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-1],rank,R24C2,gender,R22C2,JulyHC,""Y""))"
Range("B25").Copy Range("b25:b42")
Range("C25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-2],rank,R24C3,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-2],rank,R24C3,gender,R22C2,JulyHC,""Y""))"
Range("c25").Copy Range("c25:c42")
Range("d25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-3],rank,R24C4,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-3],rank,R24C4,gender,R22C2,JulyHC,""Y""))"
Range("d25").Copy Range("d25:d42")
Range("e25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-4],rank,R24C5,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-4],rank,R24C5,gender,R22C2,JulyHC,""Y""))"
Range("e25").Copy Range("e25:e42")
Range("f25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-5],rank,R24C6,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-5],rank,R24C6,gender,R22C2,JulyHC,""Y""))"
Range("f25").Copy Range("f25:f42")
Range("g25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-6],rank,R24C7,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-6],rank,R24C7,gender,R22C2,JulyHC,""Y""))"
Range("g25").Copy Range("g25:g42")
Range("h25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-7],rank,R24C8,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-7],rank,R24C8,gender,R22C2,JulyHC,""Y""))"
Range("h25").Copy Range("h25:h42")
Range("K25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-1],rank,R24C11,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-1],rank,R24C11,gender,R22C11,JulyHC,""Y""))"
Range("K25").Copy Range("k25:k42")
Range("L25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-2],rank,R24C12,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-2],rank,R24C12,gender,R22C11,JulyHC,""Y""))"
Range("l25").Copy Range("l25:l42")
Range("m25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-3],rank,R24C13,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-3],rank,R24C13,gender,R22C11,JulyHC,""Y""))"
Range("m25").Copy Range("m25:m42")
Range("n25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-4],rank,R24C14,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-4],rank,R24C14,gender,R22C11,JulyHC,""Y""))"
Range("n25").Copy Range("n25:n42")
Range("o25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-5],rank,R24C15,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-5],rank,R24C15,gender,R22C11,JulyHC,""Y""))"
Range("o25").Copy Range("o2542")
Range("p25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-6],rank,R24C16,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-6],rank,R24C16,gender,R22C11,JulyHC,""Y""))"
Range("p25").Copy Range("p2542")
Range("q25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-7],rank,R24C17,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-7],rank,R24C17,gender,R22C11,JulyHC,""Y""))"
Range("q25").Copy Range("q25:q42")
Range("a1").Select
Range("B6:H19").Value = Range("B6:H19").Value
Range("k6:q19").Value = Range("k6:q19").Value
Range("b25:h42").Value = Range("b25:h42").Value
Range("k25:q42").Value = Range("k25:q42").Value
Range("a1").Select
Application.ScreenUpdating = True
MsgBox "Data has been updated"

 

[wrightyrx7]

Might be easier telling us what this code does/show us the before and after.

Regards
Chris

 

[Joe4]

One thing that may help a little is to get rid of most of your "SELECT" statements. Most of them are unnecessary, and just the result of using the Macro Recorder.

All your lines that have this structure:

[COLOR=#333333]Range("B6").Select[/COLOR]
[COLOR=#333333]ActiveCell.FormulaR1C1 = _[/COLOR]
[COLOR=#333333]"=IF(COUNTIFS(location,RC[-1],rank,R5C2,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-1],rank,R5C2,gender,R3C2,JulyHC,""Y""))"[/COLOR]
[COLOR=#333333]Range("B6").Copy Range("b6:B19")[/COLOR]

Can be shortened like this:

[COLOR=#333333]Range("B6").[/COLOR][COLOR=#333333]FormulaR1C1 = _[/COLOR]
[COLOR=#333333]"=IF(COUNTIFS(location,RC[-1],rank,R5C2,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-1],rank,R5C2,gender,R3C2,JulyHC,""Y""))"[/COLOR]
[COLOR=#333333]Range("B6").Copy Range("b6:B19")[/COLOR]

 

[Comfy]

Copy your data and try these:

Range("B6:H19").FormulaR1C1 = _
"=IF(COUNTIFS(location,RC1,rank,R5C,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC1,rank,R5C,gender,R3C2,JulyHC,""Y""))"
Range("K6:Q19").FormulaR1C1 = _
"=IF(COUNTIFS(location,RC10,rank,R5C,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC10,rank,R5C,gender,R3C11,JulyHC,""Y""))"
Range("B25:H42").FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC1,rank,R24C,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC1,rank,R24C,gender,R22C2,JulyHC,""Y""))"
Range("K25:Q42").FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC10,rank,R24C,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC10,rank,R24C,gender,R22C11,JulyHC,""Y""))"




Range("B6:H19").Value = Range("B6:H19").Value
Range("k6:q19").Value = Range("k6:q19").Value
Range("b25:h42").Value = Range("b25:h42").Value
Range("k25:q42").Value = Range("k25:q42").Value

 

[wrightyrx7]

Comfy, so glad you posted that!

Been sat here scratching my head for about 30 minutes. I had it the same sort of way but my columns kept incrementing.

 

[Comfy]

Comfy, so glad you posted that!

Been sat here scratching my head for about 30 minutes. I had it the same sort of way but my columns kept incrementing.

Let's just see if it's what the OP was after.

I do suspect though that there maybe a better formula, but without more info about what the other ranges are etc I'm unsure.

Edit:

One handy trick when writing R1C1 formulas is to utilise the immediate window.

typing
? ActiveSheet.Range("C7").FormulaR1C1

Will display the R1C1 formula that is in C7.

 

[wrightyrx7]

That is really handy, thank you.

I have never had a reason to write code using 'RC'. But this proves writing this way is very handy, the code I was writing had a couple of loops to try and achieve this.

Love learning something new. Now i just need to get my head round writing with 'RC'.

 

[dlatha01]

Thanks Comfy. That worked a treat ! Perfect

 

[Comfy]

You're Welcome.