Solved: Looping

[kbsudhir]

Hi All

I am able to use a loop until to do a set of instructions until a blank cell is found in the column.

Ex.

[VBA]cnt = 2
do until Range("A"&cnt) = ""
msgbox hello
loop[/VBA]

But my problem is do the same in a row.

Ex.

I want execute a set of instructions till i find a blank cell in a row.
I want to change the column of the row i.e A to B to C to D like that.

Please Guide.

Thanks
Sudhir

[MaximS]

without looping you can find out the last used column
use following code:

[vba]
Dim LastColumn As Long
LastColumn = Range("IV1").End(xlToLeft).Column
[/vba]

or if you want to check each cell in a column before jumping to another column till you will find empty cell:

[vba]
' j is a column number
For j = 1 To 256
' i is a row number
For i = 1 To 65536

If Cells(i, j) = "" Then
Exit Sub
Else

End If

Next i
Next j
[/vba]

or if you want to check each cell in a row before jumping to another row till you will find empty cell:

[vba]

' i is a row number
For i = 1 To 65536

' j is a column number
For j = 1 To 256

If Cells(i, j) = "" Then
Exit Sub
Else

End If

Next j
Next i
[/vba]

[kbsudhir]

Thanks MaximS

My mistake I do not wrote my problem clearly.

But I need to pull the data of each & every cell of the row till i reach the blank cell.

Hence increment from one column to another in the row is important.

[Kenneth Hobs]

If all of the cells are not empty then you will get an infinite loop.

Typically, one iterates using For Each for a range. Methods like Find can be used to find the first blank cell.

This first is a modification of your code for column A. The second is for row 2.

One method is to use Cells. As in Test2. It starts at B2.
[VBA]Sub test()
Dim cnt As Long
cnt = 2
Do Until Range("A" & cnt) = ""
MsgBox Range("A" & cnt).Address & vbCrLf & Range("A" & cnt).Value
cnt = cnt + 1
Loop
End Sub


Sub test2()
Dim cnt As Long
cnt = 2
Do Until Cells(2, cnt) = ""
MsgBox Cells(2, cnt).Address & vbCrLf & Cells(2, cnt).Value
cnt = cnt + 1
Loop
End Sub[/VBA]

[kbsudhir]

Thanks MaximS & Kenneth for your help.