Untitled

Public Sub Workbook_Open()

    Application.OnTime Now, "TestFind"
End Sub
Public Sub TestFind()

    Dim lastRowRange As Range
    Dim wb1, wb2 As Workbook
    Dim Bottom_RowNum As Long
    Dim Left_ColumnChar, Right_ColumnChar, formula_range As String
    
    Set wb1 = Workbooks.Open("C:\Users\ieonghl\Desktop\EARLIER.xlsm")
    wb1.Worksheets("Sheet2").Activate
    
    'Set lastRowRange = FindLastRowRange(wb1.ActiveSheet.Cells)'
     Set lastRowRange = FindLastRowRange(Cells)
    lastRowRange.Select
    
    Left_ColumnChar = Col_Letter(Selection.End(xlToLeft).Column)
    Right_ColumnChar = Col_Letter(Selection.End(xlToRight).Column)
    Bottom_RowNum = Selection.End(xlToLeft).Row
    formula_range = Left_ColumnChar & Bottom_RowNum & ":" & Right_ColumnChar & (Bottom_RowNum + 5)
    
    Selection.AutoFill Destination:=Range(formula_range), Type:=xlFillDefault
    
    wb1.Save
    
End Sub
Public Sub TestFind_2()

    Dim lastRowRange As Range
    Dim Bottom_RowNum, LeftCol_num, RightCol_num As Long
    Dim formula_range As String
    
    ActiveWorkbook.Worksheets("Sheet4").Activate
    
    LeftCol_num = Range("C1").Column
    RightCol_num = Range("E1").Column
    
    Set lastRowRange = FindLastRowRange(ActiveSheet.Cells, LeftCol_num, RightCol_num)
    lastRowRange.Select
    
    Bottom_RowNum = Selection.End(xlToLeft).Row
    
    formula_range = Col_Letter(LeftCol_num) & Bottom_RowNum & ":" & Col_Letter(RightCol_num) & (Bottom_RowNum + 5)
    Selection.AutoFill Destination:=Range(formula_range), Type:=xlFillDefault
    
    ActiveWorkbook.Save
    
End Sub
Public Sub TestFind_3()

    Dim lastRowRange As Range
    Dim Bottom_RowNum, LeftCol_num, RightCol_num, CDB_BotRow_num, Current_BotRow_num As Long
    Dim formula_range As String
    
    ActiveWorkbook.Worksheets("RT_ELECTRICITY_ENERGY").Activate
    
    LeftCol_num = Range("E1").Column
    RightCol_num = Range("F1").Column
    CDB_BotRow_num = Range("A1").End(xlDown).Row
    Current_BotRow_num = Range("E1").End(xlDown).Row
    Rows_to_Drag = CDB_BotRow_num - Current_BotRow_num
    
    Set lastRowRange = FindLastRowRange(Range("E1,F1").EntireColumn, LeftCol_num, RightCol_num)
    lastRowRange.Select
    Bottom_RowNum = Selection.End(xlToLeft).Row
    
    formula_range = Col_Letter(LeftCol_num) & Bottom_RowNum & ":" & Col_Letter(RightCol_num) & (Bottom_RowNum + Rows_to_Drag)
    Selection.AutoFill Destination:=Range(formula_range), Type:=xlFillDefault
    
    ActiveWorkbook.Save
    
End Sub
Function FindLastRowRange(rg As Range, Optional a = 1, Optional b = 0) As Range

    Dim lastRow As Long, lastColumn As Long
    
    lastRow = rg.Find(What:="*" _
                                                    , Lookat:=xlPart _
                                                    , LookIn:=xlFormulas _
                                                    , searchorder:=xlByRows _
                                                    , searchdirection:=xlPrevious).Row

    lastColumn = rg.Find(What:="*" _
                                                    , Lookat:=xlPart _
                                                    , LookIn:=xlFormulas _
                                                    , searchorder:=xlByColumns _
                                                    , searchdirection:=xlPrevious).Column
    
    If b = 0 Then
         Set FindLastRowRange = Range(Cells(lastRow, a), Cells(lastRow, lastColumn))
    Else
        Set FindLastRowRange = Range(Cells(lastRow, a), Cells(lastRow, b))
    End If
    
End Function
Function Col_Letter(lngCol) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function
Function firstColumnNumber(rg As Range) As Long

    Dim firstRow, lastRow, firstCol, lastCol, leftPart, rightPart

    leftPart = Split(rg.Address(0, 0), ":")(0)
    rightPart = Split(rg.Address(0, 0), ":")(1)
    firstRow = Range(leftPart).Row
    firstCol = Range(leftPart).Column
    lastCol = Range(rightPart).Column

    firstColumnNumber = firstCol

End Function