As best I can make out, this should do it. Use column E to find the last
cell and use column A to find the last registration number.

Dim rng as Range, rng1 as Range
set rng = Cells(rows.count,1).End(xlup)
set rng1 = Cells(rows.count,5).End(xlup)
Range(rng,rng1.offset(0,-4)).Value = rng.Value

--
Regards,
Tom Ogilvy

"Chris Hankin" wrote in message
...
Hello,

Could someone please help me with the following:

I have started creating an Excel spreadsheet. In column A is a list of
registration numbers. There are many blank cells in column A underneath
each registration number. The number of blank cells underneath each
registration number varies. Sometimes there may be 2 blank cells or 30
blank cells or no blank cells. I have managed to get Excel to locate the
LastCell which has a registration number in it. I used the following
formula for LastCell:

=OFFSET('SA REGISTER'!$A$2,COUNTA('SA REGISTER'!$A$2:$A$20000)-1,0)

I used the following formula for LastCellStockcode:

=OFFSET('SA REGISTER'!$E$2,COUNTA('SA REGISTER'!$E$2:$E$20000)-1,0)

I then used the following macro to get Excel to locate the last
registration
number in column A:

Sub Rego_No_Fill()
Range("A2").Select
Application.ScreenUpdating = False
Range("LastCellStockcode").Select
ActiveCell.Offset(0, -4).Range("A1").Select
Range("LastCell").Select
End Sub

So what I would like Excel to do is:

AutoFill from the LastCell (in column A) which has a registration number
in
it to the end of the table. To find the end of the table, I use the
formula
shown above:

=OFFSET('SA REGISTER'!$E$2,COUNTA('SA REGISTER'!$E$2:$E$20000)-1,0)

For example, if I run the above-mentioned macro, it will locate the last
registration number in my spreadsheet (in column A) and that happens to be
registration number: 556. There are 2 blank cells underneath registration
number 556. I would like Excel to AutoFill those 2 blank cells with the
same registration number of: 556. Thus, all 3 cells in column A will have
the registration number of: 556.

Any help would be greatly appreciated,

Kind regards,

Chris.