How can I define/refer to this range without string concatenation?

[RobertSF]

I have a table (ListObject) with a fixed number of columns and a variable number of rows. The table is a perfect grid except there's a column that is empty for some rows and not for others. I sort the table on that column to separate the rows with values in that column from rows with empty cells in that column. Now I would like to define the range within the table that contains the header and the "complete" rows (i.e. the ones with data in all columns).

Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects(1)
tbl.Sort.SortFields.Clear
tbl.Range.Sort Key1:=Range("MyTable[MyColumn]"), Order1:=xlAscending

Dim numRows As Long
numRows = tbl.ListColumns("MyColumn").Range.Cells.Count - _
          tbl.ListColumns("MyColumn").Range.SpecialCells(xlCellTypeBlanks).Count - 1

' Would be nice if you could do something like this, but you can't:
' Set rng = tbl.Range(rows(1),rows(numRows))

As you can see in the above code, I define the table, sort it, and determine how many rows are complete (numRows). But now how do I define the range that consists of those complete rows?

I want the rows in the table (not rows all across to column IV), but only rows from 1 to whatever is in numRows. How to do this without resorting to string concatenation to build visual addresses like this?

range("A1:H" & numRows)

 

[RobertSF]

Darn... two minutes more of googling, and I found the answer. It's the resize method.

Dim tbl As ListObjectSet tbl = ActiveSheet.ListObjects(1)
tbl.Sort.SortFields.Clear
tbl.Range.Sort Key1:=Range("MyTable[MyColumn]"), Order1:=xlAscending


Dim numRows As Long
numRows = tbl.ListColumns("MyColumn").Range.Cells.Count - _
          tbl.ListColumns("MyColumn").Range.SpecialCells(xlCellTypeBlanks).Count - 1


' Would be nice if you could do something like this, but you can't:
' Set rng = tbl.Range(rows(1),rows(numRows))

'Instead do this:
Set rng = tbl.Range.Resize(numRows)

Yeah, I'm a noob.