I have a macro that takes a 9 digit number from one cell and converts it into a zipcode + 4 number.

If the number is in A1 I set focus on B1 and =left(a1,5), then in C1 I put the dash, (-) in d1 =right(A1,4) then I concatentate the 3 cells in E1, and then autofill the columns down to the last row with the 9 digit number.

My problem is that the incoming sheets can have rows fro 100 to 10,000 so in the Macro I have to set the autofill command to 10,005 to make sure I fill the sheet far enough. Of course, when I have a sheet of 100, I have to go back and delete all the dashes that filled to row 10,005.

Is there a way to autofill the inserted columns to the same row as the original column with the 9 digits in it???

Thanks

Paul