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?

Does this answer your question? [Better way to find last used row](https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row) — braX, Jan 25 '21 at 09:55

score 2 · Answer 1 · answered Jan 25 '21 at 09:58

2

Try the next way, please:

Sub testDefineRange()
 Dim ws As Worksheet, lastRow As Long, myrange As Range

 Set ws = ActiveSheet 'use here what you need
 lastRow = ws.Range("B" & ws.rows.count).End(xlUp).row
 'Defined reference ws
 Set myrange = ws.Range("B2:B" & lastRow)
 myrange.Select
End Sub

answered Jan 25 '21 at 09:58

FaneDuru

38,298
4
19
27

2

Please remove the `Select`-statement at the end or at least add a comment that this was added only to demonstrate the result. – FunThomas Jan 25 '21 at 10:01
@FunThomas: I kept it there for exactly the same reason. He can easily delete it, after having the confirmation that the code works as he needs. :) – FaneDuru Jan 25 '21 at 10:04
Yes. Of course he *could* delete it. If you would mention why you put it there in the first place. And if you would mention that this code will work only with the `ActiveSheet` (else you will get a runtime error). – FunThomas Jan 25 '21 at 10:11
@FunThomas: `Set ws = ActiveSheet 'use here what you need` should let him defining the sheet which he needs. I only tried to adapt **his code** in a way to make it working as he asked in his question. Otherwise, it could be easy to to be done in only one code line... – FaneDuru Jan 25 '21 at 10:30
And if ws is set to any other sheet, it will fail. OP doesn't mention anything about using Activesheet. And code should **never** rely on ActiveSheet (or Selection). Of course it can be done with one line (although I think it's better to split it on 2 lines and save the last row into a variable), this has been asked (and answered) 100 times here on SO. – FunThomas Jan 25 '21 at 10:38
@FunThomas: I cannot get you, sorry... Do you want me to delete the answer? If yes, this will not be a problem! But he does use a declaration (`ws as worksheet`) and he did not define it. The easiest way of showing a test code, **when it is not clear what sheet does he have in mind**, is to refer the active sheet. I consider that I spent much more time to clarify something not important than I should. I wish you a good day! – FaneDuru Jan 25 '21 at 10:43
I don't mind about if this answer stays or not. I wanted to point out the problem with the `Select`. BTW: Obviously the OP has defined `ws` somewhere, else he would have gotten a runtime error. – FunThomas Jan 25 '21 at 10:50
@FunThomas: His code could not work, anyhow! It could not work without `Set`, with a coma instead of a dot in `range("B2"),end(xldown)`. He only tried to show us a piece of code (not copied) and told us a story regarding what problem he faces. I only tried to use his code pattern and show a way to make it functional. And offer a solution to rapidly check if returns what he needs... – FaneDuru Jan 25 '21 at 10:55
I give up. But that's a common feeling about contributing to SO these days anyhow. – FunThomas Jan 25 '21 at 11:00

score 2 · Accepted Answer · answered Jan 25 '21 at 09:58

2

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

answered Jan 25 '21 at 09:58

Super Symmetry

2,837
1
6
17

1

Thank you, that seems to work. Can you explain to me, please, how this works exactly? Why not xldown? – Nenne Jan 25 '21 at 10:04
2

`xlDown` stops when it finds a blank cell. – braX Jan 25 '21 at 10:14
1

`.End(xlDown)` is the same as pressing `Ctrl`+`Down Arrow`. `.End(xlUp)` is the same as pressing `Ctrl`+`Up Arrow`. In this code, you are essentially going to the last cell in column B and pressing `Ctrl`+`Up` and the you use the cell you end up with. Does this suffice? – Super Symmetry Jan 25 '21 at 10:14

score 1 · Answer 3 · answered Jan 31 '21 at 15:40

I have found this link to be very useful on MANY occasions.

https://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba

Ways To Find The Last Row

Sub FindingLastRow()
'PURPOSE: Different ways to find the last row number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

'Using Find Function (Provided by Bob Ulmas)
  LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

'Using SpecialCells Function
  LastRow = sht.Cells.SpecialCells(xlCellTypeLastCell).Row

'Ctrl + Shift + End
  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row

'Using Table Range
  LastRow = sht.ListObjects("Table1").Range.Rows.Count

'Using Named Range
  LastRow = sht.Range("MyNamedRange").Rows.Count

'Ctrl + Shift + Down (Range should be first cell in data set)
  LastRow = sht.Range("A1").CurrentRegion.Rows.Count

End Sub

Ways To Find The Last Column

Sub FindingLastColumn()

'PURPOSE: Different ways to find the last column number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastColumn As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

'Ctrl + Shift + End
  LastColumn = sht.Cells(7, sht.Columns.Count).End(xlToLeft).Column

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column

'Using Table Range
  LastColumn = sht.ListObjects("Table1").Range.Columns.Count

'Using Named Range
  LastColumn = sht.Range("MyNamedRange").Columns.Count

'Ctrl + Shift + Right (Range should be first cell in data set)
  LastColumn = sht.Range("A1").CurrentRegion.Columns.Count

End Sub

score 0 · Answer 4 · answered Jan 25 '21 at 10:11

0

If it suits your case, I like to use CurrentRegion with Intersect to eliminate the title row.

with ws.range("b2")
   set myRange = intersect(.currentregion, .currentregion.offset(1,0))
end with
debug.print myRange.address

answered Jan 25 '21 at 10:11

iDevlop

24,841
11
90
149

How to dynamically select a range VBA

4 Answers4