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:
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
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.