VBA .End(xlDown) Stopping at Blank Cell

Question

I want to select the entire Ticker column but what I have now is stopping once it hits a blank cell. How do I get the entire column plus blank cells. I know I have to use xlUp but not sure what is the best way to implement it.

Set TickerHeaderCell = mpWS.Cells.Find("Ticker", LookAt:=xlWhole)
Set PriceHeaderCell = mpWS.Cells.Find("Price", LookAt:=xlWhole) 

'Set the ranges based on where the Ticker and Price headers are then go down r amount of rows to grab all cells
r = TickerHeaderCell.End(xlDown).Row
Set rngMarketPrice = mpWS.Range(PriceHeaderCell.Row & ":" & PriceHeaderCell.Column).Find("Price", LookAt:=xlWhole).Resize(r)
Set rngMarketTickers = mpWS.Range(TickerHeaderCell.Row & ":" & TickerHeaderCell.Column).Find("Ticker", LookAt:=xlWhole).Resize(r)

My favorite way to handle these range problems is to use CurrentRegion which is the equivalent of ctrl+shift+8, plus a column reference that can be made dynamic if you like: ‘Set rngMarketPrice = mpWS.Cells(1,1).CurrentRegion.Columns(1)’ — Evan Friedland, Feb 14 '21 at 18:37

score 2 · Accepted Answer · answered Feb 14 '21 at 17:09

2

Try replacing of

r = TickerHeaderCell.End(xlDown).Row 'it will return the cell above the first empty one

with

r = mpWS.cells(mpWS.rows.count, TickerHeaderCell.Column).End(xlup).Row

answered Feb 14 '21 at 17:09

FaneDuru

38,298
4
19
27

Perfect thank you! – jrocc Feb 14 '21 at 17:30

score 0 · Answer 2 · answered Feb 14 '21 at 17:20

Use xlUp instead of xlDown and start at the bottom of the column.

Set tickerHeadercell = mpWS.Cells.Find("Ticker", LookAt:=xlWhole)
Set PriceHeadercell = mpWS.Cells.Find("Price", LookAt:=xlWhole)

'Set the ranges based on where the Ticker and Price headers are 
'then come from bottom to get last cell with data
With mpWS
    Set rngMarketPrice = mpWS.Range(PriceHeadercell, .Cells(.Rows.Count, PriceHeadercell.Column).End(xlUp))
    Set rngMarketTickers = mpWS.Range(tickerHeadercell, .Cells(.Rows.Count, tickerHeadercell.Column).End(xlUp))
End With

VBA .End(xlDown) Stopping at Blank Cell

2 Answers2