[SOLVED] vba variable range of nonblank cells in column listobject

vba variable range of nonblank cells in column listobject

I have a funciton that gets a Range as variable and run over the values of the cells of the range. I need to pass ranges to taht function built as follows:

Dim hltTbl As ListObject
Set hltTbl = ThisWorkbook.Sheets("classy").ListObjects(1)
Dim i As Integer
Dim lastrow As Integer
Dim highlightsRange As Range


'range is in highlights from 1 to the first empty
For i = 1 To hltTbl.ListRows.Count
    If hltTbl.ListColumns("highlights").DataBodyRange.iTem(i).Value = "" Then
       lastrow = i - 1
       Exit For
    End If
Next

Set highlightsRange = hltTbl.ListColumns("highlights").DataBodyRange.iTem(1).resize(lastrow,0)

This does not work. What I pretend is to build the range consisting in all the cells non blank of the listobject column. (note: the cells form a continum i.e. there are no empty cells between non blank ones). What I did finding the first non-empty cell, which will be the last cell of the range and I tried to reshape the range of the first cell of the column with reshape(lastrow,0). This does not work.

Questions: a) how can i do it b) why this does not work c) (I am trying not to use filters, they are a real pain)

Thanks a lot.

EDIT1: .SpecialCells(... does not seem to include nonblanks, but only blanks edit2: this seems to work:

 DataBodyRange.SpecialCells(xlCellTypeConstants, xlTextValues)

but it the values are integers...

Solution

You should resize like this: .resize(lastrow, 1) instead of .resize(lastrow, 0). The reason is that method "resize" takes number of rows and columns of desired range size. Any range has at least 1 row and 1 column.