Record macro - Enter formula after last column with data

dovl

New Member
Joined
Apr 6, 2021
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
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"
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,898
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Does this do it for you...
It only does row 1 at the moment, but could be expanded to dop all rows if required.
VBA Code:
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
 

Forum statistics

Threads
1,141,402
Messages
5,706,245
Members
421,433
Latest member
yash0468

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top