Microsoft Excel - Loop Through Columns - Asked By Rajender Prasad on 03-Jul-13 11:00 AM

hi,

I would request provide some pointers to looop through column values.

I have some values, I have to check whether A2 is blank or not, if is blank paste the value if is not blank paste the value in B2, if B2 is not blank paste in C2 and so on..

Harry Boughen replied to Rajender Prasad on 03-Jul-13 05:59 PM

Hello Prasad,
Try

Sub PasteEmpty()

Dim varPasteValue As Variant
Dim rngCellCheck As Range

Set rngCellCheck = Range("A2")
varPasteValue = rngCellCheck.Value
Set rngCellCheck = rngCellCheck.Offset(0, 1)
nextCell:
If IsEmpty(rngCellCheck) Then
    rngCellCheck.Value = varPasteValue
Else
    Set rngCellCheck = rngCellCheck.Offset(0, 1)
    GoTo nextCell
End If

Regards
Harry

Rolf Jaeger replied to Harry Boughen on 12-Aug-13 07:17 PM

Hi Harry:

there is nothing fundamentally wrong with the solution you are proposing. However using labels is generally frowned upon, because using them tends to create unmanageable spaghetti code. I would suggest to rather us the Do/Loop syntax, see below.

Best wishes,
Rolf

Dim varPasteValue As Variant

Dim rngCellCheck As Range

Set rngCellCheck = Range("A2")

varPasteValue = rngCellCheck.Value

Do

Set rngCellCheck = rngCellCheck.Offset(0, 1)

If IsEmpty(rngCellCheck) Then

rngCellCheck.Value = varPasteValue

Exit Do

End If

Loop

Harry Boughen replied to Rolf Jaeger on 12-Aug-13 07:42 PM

Hi Rolf,
Thanks for that. I guess I rushed in a bit to give a quick response and didn't necessarily take time to think through to the best solution. I'll keep your advice in mind.
Regards
Harry