VBA Code: Auto Filling Option

[mrsriexcel]

Hi,
From the below code, the highlighted code is auto filling the cells from G6 to G511 and I am hardcoding the values.

Instead of that is there anyway I can apply Ctl+Shift+Down arrow Function?

or

Is there anyway same formula in G6 can be applied to the no.of cells based on data present in its adjacent column (F)

Range("G6").Select ActiveCell.FormulaR1C1 = "=HPLNK(RC4,RC1,R1C,RC6,R2C,R3C,R4C)"
Selection.AutoFill Destination:=Range("G6:G511")
Range("G6:G511").Select

Regards,
Sri

 

[Joe4]

Try:
Selection.AutoFill Destination:=Range("G6:G" & Range("F6").End(xlDown).Row)

 

[AlphaFrog]

This auto-fills down from G6 based on the values in column F

Range("G6:G" & Range("[COLOR=#ff0000][B]F[/B][/COLOR]6").End(xlDown).Row).FormulaR1C1 = "=HPLNK(RC4,RC1,R1C,RC6,R2C,R3C,R4C)"

No need to .Select or use the AutoFill function.

 

[Joe4]

Good point. I should have used column F, not column G in my code. I'll amend that.
And good point about not needing Selects...

 

[mrsriexcel]

Its working good.

Thank You,


I have 2 small questions:

1. from the below code, it is just making the sum of G6:G511.
But instead of that is there anyway we can add all cells from G6 to end (Ctl+Shift+Down Arrow) depends on adjacent column F?

ActiveCell.FormulaR1C1 = "Total"
Range("L3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[3]C[-5]:R[508]C[-5])"

2. If I want to mark a column with Font color (Blue/Green), how can I achieve it? I am using below code but it is showing errors.

ActiveWindow.SmallScroll Down:=-3
Columns("G:G").Select
With Selection.Font
.Color = -65536
.TintAndShade = 0


Regards,
Sri

 

[AlphaFrog]

1.
Range("L3").Formula = "=SUM(G6:G" & Range("F6").End(xlDown).Row & ")"

2.
Columns("G:G").Font.ColorIndex = 14 '14 = Teal or 42 = Aqua

 

[mrsriexcel]

Thanks Again.


Regards,
Sri