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
To set the reference, we need to use the “SET” keyword and enter the cell addresses by using the RANGE object. Now the variable “Rng” refers to the cells A1 to B5. Instead of writing the cell address Range (“A1:B5”), we can simply use the variable name “Rng.”
The Range object can consist of individual cells or groups of cells. Even an entire row or column is considered to be a range. Although Excel can work with three dimensional formulas the Range object in VBA is limited to a range of cells on a single worksheet.
To use a range or a single cell as a variable, first, you need to declare that variable with the range data type. Once you do that you need to specify a range of a cell to that variable using the range object. This also gives you access to all the properties and methods that you can use with a range.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With