vba incrementing a range's row

this should be a really quick question.

Is there any way to increment the value of a cel开发者_高级运维l.Row? For example, can I say something like usedCell.Row = usedCell.Row + 1?

That particular format doesn't work, but is there another way to increase the row by 1?

I believe cell.Offset(1,0) is what you are looking for.

The Row and Column properties of a cell (i.e. Range) are read-only so you can't increment them directly.

If you want to move through the cells in column A then iDevlop's answer works fine. An alternative method is to use the Cells method of the Worksheet object. Example code to write the word "hello" into every cell in column A from row 1 to 100:

Dim lRow As Long

For lRow = 1 To 100
    Worksheets("Sheet1").Cells(lRow, 1).Value = "hello"
Next lRow

As you can see in the example, the Cells method takes the row number as the first parameter and the column number as the second parameter.

For the simple case of dealing with cells in the same column, you could also use the Range property of the Worksheet object and construct the actual address - e.g. A39 - each time:

Dim lRow As Long

For lRow = 1 To 100
    Worksheets("Sheet1").Range("A" & lRow).Value = "hello"
Next lRow

Have you considered using

Dim c as Range
For Each c in Range("a:a")
    ...
Next c

?

You may be looking for

Set rng = rng.Resize(RowSize, ColumnSize)

In your case specifically

Dim rng_usedCells As Range

Set rng_usedCells = ActiveSheet.Range("A1:B10")

ActiveSheet.Range("D2").Value = rng_usedCells.Rows.Count
ActiveSheet.Range("E2").Value = rng_usedCells.Columns.Count

Set rng_usedCells = rng_usedCells.Resize(3, 4)

ActiveSheet.Range("D3").Value = rng_usedCells.Rows.Count
ActiveSheet.Range("E3").Value = rng_usedCells.Columns.Count