How to dynamically select a range VBA

Question

My question is very similar to the following:

Excel VBA code to select non empty cells However, I have some empty cells in between. In particular, I'm trying to define a range where the first row needs to be excluded, since it's a header. I also have to exclude the empty cells that follow. I was trying something like

Dim wb as workbook, ws as worksheet
Dim myrange as range

'Defined reference wb and ws
myrange=ws.range("B2",range("B2"),end(xldown))

But this only works if there are not empty cells in between. So, is there a fast and simple way to dynamically select a range that includes non-empty cells, excepted the header?

question from:https://stackoverflow.com/questions/65882328/how-to-dynamically-select-a-range-vba

Answer 1

Try this

'Defined reference wb and ws
Set myrange = ws.range("B2", ws.Range("B" & Rows.Count).End(xlUp))

Don't forget to use the Set keyword