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