Limit formatting in imported Macro to row numbers

[Alex501]

Hi All

I recorded a macro to format some data I use regularly. The original sheet I recorded the macro with had 161 rows.

When I've next run the macro on a second sheet with 80 rows the formatting extends beyond the rows to row 161. The column formatting was to add a country code of 44 to every line, however i now have 44 in 81 extra rows where the rest of the row is blank.

How do I edit the VBA code so it limits the formatting to the number of rows?

I assume I need to edit the following somehow;

ActiveCell.FormulaR1C1 = "Country Code"
Range("E2").Select
ActiveCell.FormulaR1C1 = "44"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E161")

Thanks

 

[Joe4]

Without seeing your data, this is the best guess I can make:

Range("E1") = "Country Code"
'Find last row with data in column D
Dim lr as Long
lr = Cells(Rows.Count, "D").End(xlUp).Row
'Populate column E down to last row
Range("E2:E" & lr) = "44"