Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel vba : Type range and cell

Tags:

excel

vba

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
like image 813
bsr Avatar asked Feb 26 '12 18:02

bsr


People also ask

How do you define a range and cell in VBA?

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.”

Can I use cells in range VBA?

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.

How do you assign a range of cells to a variable in VBA?

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.


1 Answers

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
like image 179
Doug Glancy Avatar answered Sep 23 '22 23:09

Doug Glancy