Adjusting for Number of Rows

[HazatB]

Hello, I have the below code to use Workbook (UMROI_Standard Cost Audit Reports.xlsm) Worksheet (Account To Component Audit) and clear the data from Row 9 & Column A to F all the way to the last row. I then open up my source Workbook(ent_component_audit_umroi.txt) Worksheet (ent_component_audit_umroi) and copy the data from Row 1 & Column A to F all the way to the last row then paste the copied data in Workbook (UMROI_Standard Cost Audit Reports.xlsm) Worksheet (Account To Component Audit) on Row 9 & Column A to F all the way to the last row. The problem I am facing is I need to be able to adjust for the need of less or more rows to include all the new data, I attempted to work in the LastRow function into my code however ran into debugging issue. Below is my code functioning to work if the number of rows do not change.

Sub Macro13()
'
' Macro13 Macro
'
'
    Range("A9:F9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A9:F147").Select
    Selection.ClearContents
    Workbooks.OpenText Filename:= _
        "K:\1900\dwprod1900\data\export\general\ent_component_audit_umroi.txt", Origin _
        :=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _
        False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 1) _
        , Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)), _
        TrailingMinusNumbers:=True
    Range("A1:F1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("UMROI_Standard Cost Audit Reports.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[MARK858]

Totally untested and unsure about the .txt extension.

Sub Macro13()
   
    With Workbooks("UMROI_Standard Cost Audit Reports.xlsm").Worksheet("Account To Component Audit")
        .Range("A9:F" & .Columns("A:F").Find("*", , xlValues, , xlRows, xlPrevious).Row).ClearContents
    End With

    Workbooks.OpenText Filename:= _
                       "K:\1900\dwprod1900\data\export\general\ent_component_audit_umroi.txt", Origin _
                                                                                               :=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
                                                                                                                                                         , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _
                       False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 1) _
                                                                                        , Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)), _
                                                                           TrailingMinusNumbers:=True
    With Workbooks("ent_component_audit_umroi.txt").Worksheets("ent_component_audit_umroi")
        .Range("A1:F" & .Columns("A:F").Find("*", , xlValues, , xlRows, xlPrevious).Row).Copy
    End With

    Workbooks("UMROI_Standard Cost Audit Reports.xlsm").Worksheets("Account To Component Audit").Range("A9").PasteSpecial Paste:=xlPasteValues

End Sub

 

[HazatB]

Thanks I was able to test it to work my code.