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.