identify last row containing text in column B and use row number in cell range

Hello Excel Forum gurus,

I was wondering if you'd be able to help me with a VBA script that I'm trying to create.

I want to identify what the last row containing any text in column B is, then use this row within a cell range to automatically apply a formula to row H.

The bit I'm having trouble with is "Range("H2:HLastRow").Select". Naturally, I know this command won't work but it displays what I'm trying to achieve.

My VBA is as follows:


Sub LastRowInOneColumn()

'Find the last used row in a Column: column B in this example
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
'Select the cell range of H2 to H with the last row of column B
Range("H2:HLastRow").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown

End Sub

Many thanks in advance for your help,

Matt

Thanks guys, that's spot on!

Apologies for not inserting the code correctly. I'll make sure that it's displayed correctly next time.

Many thanks,

Matt