Using Range in VBA and Excel

Question

The following is valid in VBA

Range("K1:K5")

Why is the following not valid?

Dim nb As Long
Range("K1:K" + nb)

I also tried Range("K1:K" & nb) and even tried making nb a String instead.

Answer 1

I'm not sure what the value is for nb.

Try using the Cells(RowIndex, ColumnIndex) notation where RowIndexand ColumnIndex are integers.

Sub RangeExemple()

Dim colIndex, colIndex2, rowIndex, rowIndex2 As Integer

colIndex = 7
colIndex2 = colIndex
rowIndex = 1
rowIndex2 = 5

Range(Cells(rowIndex, colIndex), Cells(rowIndex2, colIndex2)).Interior.ColorIndex = 37

End Sub

What do you want to do actually?

Answer 2

Because the value of nb = 0 and the rows in excel start with 1

Try this

Dim nb As Long
Dim Rng As Range

nb = 2
Set Rng = Range("K1:K" & nb)