excel vba : selected cells loop

excel, vba

Solution

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)

Problem

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?

Original source