What is for each loop in VBA?

What is for each loop in VBA?

A for each loop in VBA is a way of interating
through a range of cells in your worksheet.

For instance, below is my data, I need to place in a column.

Here I am adding a button called “Put In Column”

You will need to assign the “ForEachLoop” macro to the new button.

Then I just select the cells on row “A”, I want to put into a column and click my button!

Now all my row data is in the column (look at the image):

Here is the code:

Sub ForEachLoop()
    Dim rngSelection As Range
    Dim rngCell As Range
    
    Dim intRow As Integer
    
    Set rngSelection = Selection.Cells 'Whatever I selected is the Selection
    
    'start at row
    intRow = 3
    
    For Each rngCell In rngSelection
        Range("A" & intRow) = rngCell.Value
        intRow = intRow + 1
    Next
    
    
End Sub

I know you can use the Copy command and choose the Transpose option of Paste Special,
but this is an example, and I was actually using this to speed the process of generating the create
table SQL syntax on CSV files I was receiving.

Watch me do it:

Let me know if you need help.


Posted

in

by

Tags: