Would like to iterate each row in a selection in excel VBA.
I have:
Dim rng As Range
Dim s As String
Set rng = Application.Selection
Debug.Print "c :" & rng.Address
This prints
c :$B$22:$C$29
How to make a loop from row 22 to 29/ parse out the start and end row?
And in separate int variables get the start and end column?
A general solution to looping through a range of cells in Excel:
Sub LoopSelection()
Dim cel As Range
Dim selectedRange As Range
Set selectedRange = Application.Selection
For Each cel In selectedRange.Cells
Debug.Print cel.Address, cel.Value
Next cel
End Sub
Iterates from top-left most cell, across then down.
Iterates directly over rows only. I have more than one column selected, wanted outer loop rows only.
Option Explicit
'go over selection, merge text in cells, so all in first column in every row.
Sub mergeCombine()
Dim rng As Range
Dim s As String
Set rng = Application.Selection
Debug.Print "c :" & rng.Address
s = rng.Column
Dim cRow As Range
Debug.Print "c :" & rng.Address & "||" & rng.AddressLocal
Dim ir, ic As Integer
For ir = 1 To rng.Rows.Count
Set cRow = rng.Rows(ir)
s = ""
For ic = 1 To rng.Columns.Count
s = s & cRow.Columns(ic).Text
Cells(cRow.Row, cRow.Columns(ic).Column).Formula = ""
If (ic + 1) <= rng.Columns.Count Then
s = s & " "
End If
Next
Cells(cRow.Row, cRow.Column).Formula = ""
Cells(cRow.Row, cRow.Column).Formula = s
Next
End Sub
It doesn't work with non-contiguous selection. Count is incorrect with non-contiguous, you'd have to use For Each cRow In rng.Rows I have not tried this myself, my use case was for a contiguous only. Thank you Danny Holstein (comment in 2019)
For Each cRow In rng.Rows