How to find last row and paste the formula to the last cell of column

Question

Need help to find the last row in column and paste the H2 formula up to last column till the data is present in last row. Used below code but not getting success. Also code will automatically add column name Mid value in H column. Example if cell H2 contains a formula of mid(G2,20,2) will give P2 value. And that formula should copy till last used row of that column and paste the formula.

Please change the destination of source file according to your convenient.

I would be great full if you can help.

Thank you Regards, Amit Singh

Code Snipped:-

Sub Timecalculation()

Dim wb As Workbook
Dim wks As Worksheet
Dim objList As ListObject
Dim LastRow As Long
Dim sht As Worksheet
    
Set wb = Workbooks.Open("C:\Users\611892636\OneDrive - BT Plc\Desktop\Nestle JML\Mansi Work\Test\SourceData.xlsx")
wb.Sheets("Data").Activate
For Each wks In ActiveWorkbook.Worksheets
    For Each objList In wks.ListObjects
        objList.Unlist
     Next objList
Next wks
Range("H1").Select

'adding column for Mid Value
Set sht = ActiveSheet
Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H1").Value = "Mid Value"
ActiveSheet.Range("H2").Select
ActiveCell.FormulaR1C1 = "=Mid(RC[-1],20,2)"
Selection.Copy
Range("H3").Select
LastRow = Range("H" & Rows.Count).End(xlUp).Row
Range("H2:H" & LastRow).Formula = "=&H2"
Range("I1").Select

ActiveSheet.AutoFilterMode = False

'Adding column for time calculate
Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I1").Value = "Time Cal"
Range("J1").Select

'Adding column for time in Minutes
Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("J1").Value = "Time In Minutes"
Application.DisplayAlerts = False


End Sub

Dominique Dominique · Accepted Answer · 2020-09-08T15:50:17

Normally you can find the last using this piece of code:

Range("H2").End(xlDown)

Good luck

Edit after comment from Amit

Apparently this seems to be the solution:

Range("H2:H" & Cells(Rows.Count, 1).End(xlUp).Row).Formula = "=Mid(RC[-1],20,2)"

How to find last row and paste the formula to the last cell of column

1 Answers