CODEWITHSUNDEEP
vbaexcel
jz3
jz3

Reputation: 521

vba incrementing a range's row

this should be a really quick question.

Is there any way to increment the value of a cell.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?

Upvotes: 2

Views: 75883

Answers (4)

Ben F.
Ben F.

Reputation: 107

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

rng.Resize

Upvotes: 0

barrowc
barrowc

Reputation: 10679

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

Upvotes: 6

iDevlop
iDevlop

Reputation: 25262

Have you considered using 

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

?

Upvotes: 1

Excellll
Excellll

Reputation: 5785

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

Upvotes: 10

Related Questions