Excel vba : Type range and cell

Question

I am bit confused by cell and range in vba (excel). Logically, i could think a cell as a range with size = 1; and I think it is easy to make a range out of a cell.

If I read the api of EntireRow property here it operates on range. But, the below code work, indicating 'cell'variable inside the loop is a range

Set import = Sheets("import")
    Set spRange = import.Range("A2")
    Set spRange = import.Range("A2:" & spRange.End(xlDown).Address)
    For Each cell In spRange
        dict.Add cell.Offset(0, 2).Text, cell.EntireRow
    Next cell

At the same time, the below code returns an error indicating type mismatch when call the removecell function. What should be the type of targetCell in the function definition?

Set spRange = mySheet.Range("b2", mySheet.Range("b2").End(xlDown))
For Each cell In spRange
    val = removecell (cell)
Next cell


Public Function removecell(targCell As Range) As Boolean
    removecell = False
End Function

You are right. Cell is a Range with 1 item. Is val, of type Boolean? — shahkalpesh
yes. this method was previously working when I passed "cell.Text", and accept it in the remove method cellVal As String. Now I am trying to pass the cell (range) itself by passing cell instead of cell.Text — bsr

Doug Glancy Doug Glancy · Accepted Answer · 2012-02-26T18:36:44

This compiles and runs:

Sub Tester()
Dim spRange As Excel.Range
Dim cell As Excel.Range
Dim mySheet As Excel.Worksheet
Dim val As Boolean

Set mySheet = ActiveSheet
Set spRange = mySheet.Range("b2", mySheet.Range("b2").End(xlDown))
For Each cell In spRange
    val = removecell(cell)
Next cell

End Sub

Public Function removecell(targCell As Range) As Boolean
    removecell = False
End Function

Excel vba : Type range and cell

1 Answers