Code help on range cell objet, general information

[brodaddy2002]

I am trying to understand the code below. Can anyone hep me understand line 3?
This came from a Userform with ListBox1 Click event.

Private Sub ListBox1_Click()
If ListBox1.ListIndex <> -1 Then
Range(Cells(ListBox1.ListIndex + 2, 1), Cells(ListBox1.ListIndex + 2, _ 3)).Select
End If
End Sub

The code adds items to a listbox if the listindex equals -1. I am just trying to figure out what range the code selects.

 

[Colin Legg]

Hi,

Range(Cells(ListBox1.ListIndex + 2, 1), Cells(ListBox1.ListIndex + 2, 3)).Select

Let's suppose that Listbox1.ListIndex is equal to three. Then we get:

Range(Cells(5, 1), Cells(5, 3)).Select


The 5s refer to the row index of the cells. The 1 and the 3 refer to the column index of each cell. So this will select the range A5:C5. (A=1 and C=3).

 

[brodaddy2002]

Hi,

Range(Cells(ListBox1.ListIndex + 2, 1), Cells(ListBox1.ListIndex + 2, 3)).Select

Let's suppose that Listbox1.ListIndex is equal to three. Then we get:

Range(Cells(5, 1), Cells(5, 3)).Select


The 5s refer to the row index of the cells. The 1 and the 3 refer to the column index of each cell. So this will select the range A5:C5. (A=1 and C=3).

Ok, but I just want to make sure I understand. Cells(5,1) refers to select 5 rows in A (column1), and Cells(5,3) refers to 5 rows in C (Column 3). What does range.range do? Is that even doable?

 

[Colin Legg]

I know it's a bit confusing because of the 's' on the end, but Cells() returns a reference to a single cell.

In this case, it's implicit that the Cells() property belongs to the worksheet, so the reference it returns is relative to cell A1.
Cells(5,1) returns a reference to the cell in the 5th row and 1st column = A5
Cells(5,3) returns a reference to the cell in the 5th row and 3rd column = C5

Range(Cells(5,1), Cells(5,3)) returns a reference to A5:C5. For more information have a look at the 'Range Property' topic in the VBA helpfile.


Yes, you can also have Range.Range because the Range class has a Range property. For example, Range("B2").Range("B2") will return a reference to range C3.That's quite confusing so it's quite unusual to see people using Range.Range.

 

[Colin Legg]

I know it's a bit confusing because of the 's' on the end, but Cells() returns a reference to a single cell.

That should have read:

I know it's a bit confusing because of the 's' on the end, but Cells(5,1) and Cells(5,3) each return a reference to a single cell. This is a bit of a white lie but, for convenience, consider Cells(5,1) to be a shorthand way of writing Cells.Item(5,1) and Cells(5,3) to be Cells.Item(5,3).