Incrementing the column at regular intervals

Closed
Report
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
Hello,


I need to increase the cell range at regular intervals using macros.

My code is:
Do While Cells(2, m).Value <> ""
Range("cells(2, m).value:cells(18,n).vlaue").Select
m = m + 1
n = m + 18
Loop

It is showing bug.
What is wrong rang increment process
Can some one can help in this.

1 reply

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Arun,

Not exactly sure what you are trying to do, but this line doesn't make sense:
Range("cells(2, m).value:cells(18,n).vlaue").Select

Correct way would be:
Range(cells(2, m),cells(18,n)).Select

Also m = 0 until the code comes to the line m = m +1.
As long as m = 0, the range Cells(2, m) is invalid as Colum 0 doesn't exist.

If you want to select the 18 column to the right of your used range for rows 2 to 18, then a loop doesn't seem to be the most effective way.

Try something like:
Dim lCol as integer
lCol = cells(2,columns.count).end(xltoleft).column +1
range(cells(2,lcol),cells(18,lcol+18)).select

Hopefully this is helpful to you.

Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
0