Change a specific code to generic in VB macro

[Sue]

I have the following:

Selection.AutoFill Destination:=Range("BE2:BE567"), Type:=xlFillDefault
Range("BE2:BE567").Select

I would like to change the code to include all possible used cells to fill.
How do I do this?

 

[gt]

Hi Sue:

Here's one approach.

Use a line of code like this to find the last used row:

LastRow = Range("B65536").End(xlUp).row OR
LastRow = ActiveCell.SpecialCells(xlLastCell).row

Then modify your code to this:

Selection.AutoFill Destination:=Range("BE2:BE" & Lastrow), Type:=xlFillDefault
Range("BE2:BE" & Lastrow).Select

Give that a whirl.

cheers,
gt

 

[gt]

ooops - one slight change :

LastRow = Range("BE65536").End(xlUp).row OR
LastRow = ActiveCell.SpecialCells(xlLastCell).row

Then modify your code to this:

Selection.AutoFill Destination:=Range("BE2:BE" & Lastrow), Type:=xlFillDefault
Range("BE2:BE" & Lastrow).Select