Record macro - Enter formula after last column with data

[dovl]

Hello,

I am new to recording macros, and I'm not clear with understanding how my actions get recorded.
I am recording a macro for a data set that constantly expands in rows and columns. I attempted to do a simple Count formula entered after the last column and the formula should count the cells within the range of the fifth column (E - Constant) though the second to last column (Elastic). I attempted this by simply selecting "Control + Right Key" and then "Right Key" in the hopes that the Macro will record the action of going to the last column in the range. Then I entered =count > tab > Hit the left arrow twice > Control + Shift +Left arrow > Right arrow 3 times (to get to column E as starting point. Unfortunately the macro recorded it as follows.

Range("A1").Select
Selection.End(xlToRight).Select
Range("EK1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Count"
Range("EK2").Select
ActiveCell.FormulaR1C1 = "=COUNT(RC[-139]:RC[-2])"
Range("EK3").Select
End Sub

Please advise where I am going wrong, and what is the correct way to do this. It seems the macro is recording the cell EK1 as apposed to "the cell after the last cell in data set"

 

[Michael M]

Does this do it for you...
It only does row 1 at the moment, but could be expanded to dop all rows if required.

Sub MM1()
Dim lc As Integer
lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
With Range(Cells(1, 1), Cells(1, lc))
    .Borders.LineStyle = xlNone
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
End With
Cells(1, lc + 1).Value = "Count"
Cells(1, lc + 2).Formula = "=COUNT(A1:A" & lc - 2 & ")"
End Sub