VBA dynamic range and autofill based on named range

[aivoryuk]

Hi I need a little help with some VBA coding.

I have used the following to find the last cell.

Sub LastCellInColumn()
Range("B65000").End(xlUp).Select

This works fine.

However when I substitute the B65000 to a named range "chttotal" it just gives me cell B1.

How can I get around this?

The 2nd part of this is I am importing a spreadheet onto another sheet which uses named ranges. On the spreadheet to where the data is imported to there is a column where there is a formula. I would like this formula to update (autofill) as a named range ("chttotal") or to the length of the column that has been imported.

hope this makes sense

 

[John Davis]

Hi I need a little help with some VBA coding.

I have used the following to find the last cell.

Sub LastCellInColumn()
Range("B65000").End(xlUp).Select

This works fine.

However when I substitute the B65000 to a named range "chttotal" it just gives me cell B1.

How can I get around this?

The 2nd part of this is I am importing a spreadheet onto another sheet which uses named ranges. On the spreadheet to where the data is imported to there is a column where there is a formula. I would like this formula to update (autofill) as a named range ("chttotal") or to the length of the column that has been imported.

hope this makes sense

Does this work?

Range("chttotal").Cells(rows.count,2).End(xlUp).select

 

[aivoryuk]

Does this work?

Range("chttotal").Cells(rows.count,2).End(xlUp).select

Hi ues it does although I have to change it slightly (rows.count,1)

I did manage to find this out

Range("chttotal").Select
Selection.End(xlDown).Select

but if the if cell B1 is the only cell to have data in it jumps to to the last cell in "chttotal" not sure why that is.

Any idea on once I've selected the last cell in the named range I can then get it to autofill to the same number of cells as another named range?