TechHelpList.com

In Excel, Have you ever wanted to go from something like this:

To the same data all in one row:

Then you can use some VBA to do it!

Included in this tutorial:

For Each and Do While loops in VisualBasic, VBA, VBScript.

Looping through columns in Excel.

Copying Values in Excel.

Some Visual Basic for Applications.

To Loop thru Row 1, a column at a time (cell), breaking out of the loop when you find an empty:

Public Sub LoopCellsInRow()
   Dim cell as Range
 For Each cell In Range("1:1")
      If cell.Value = "" Then
         MsgBox "Found empty at " & cell.Address
         Exit For
      Else
         'doSomethingUseful( cell.Value )
      End If
   Next cell
End Sub

To Loop thru Column A, a row at a time (cell), breaking out of the loop when you find and empty:

Public Sub LoopCellsInColumn()
   Dim cell as Range
 For Each cell In Range("A:A")
      If cell.Value = "" Then
         MsgBox "Found empty at " & cell.Address
         Exit For
      Else
         'doSomethingUseful( cell.Value )
      End If
   Next cell
End Sub

Finally, Loop thru cells in a block and copy them to one long row:

Public Sub LoopBlock()

Dim col As Range
Dim j, I, done As Integer
done = 0
j = 1
I = 5


Do While done = 0
   For Each col In Range(I & ":" & I)
         'col.Activate        'useful for
         'Sleep (200)       'debugging
        If col.Value = "" Then
            If col.Column = 1 Then
                done = 1          'the terminating clause
            End If
                
          'MsgBox "empty at " & col.Address
          Exit For
        Else
            
            Cells(4, j) = col.Value
            'MsgBox "put " & col.Address & " " & col.Value
            j = j + 1
          
        End If
    Next col
    I = I + 1
Loop
    
MsgBox "done"

This script expects the input to start at row5, putting the output on row4. This leaves room for a button above that. Obviously you can make a form input with your script to ask the user WHERE to dump the output.

Simple nested For Each loop in a Do While loop in Visual Basic.

Looking for adjacent values in Excel with VBA. Using .offset()

Someone wanted to message alert when One cell is N and the next cell to the right was J. For example.

A B

C D

E F

N J

Yourmom Taco

We could loop down col A and do something if a.value was N AND b.value was J.

Sub findNJ()

 For Each currCell In Range("A:A")
    currCell.Activate
 
    If currCell = "" Then
         'MsgBox "Found empty at " & cell.Value
         Exit For
    Else
        If currCell.Value = "N" And currCell.Offset(0, 1).Value = "J" Then
            MsgBox "gtfo!"
        End If
        
    End If
Next currCell
End Sub

The useful part here is the .offset function. we loop down col A until we find a cell where it is N AND it's offset by 1 col is J. I'm 100% sure there are better ways to do this. But the quick and dirty version, and it works. So GTFO.

If this article was at least a little helpful, how about a +1?