Excel VBA: 循环选定的单元格

Question

希望能在Excel VBA中迭代选择区域中的每一行。

我有：

Dim rng As Range
Dim s As String
Set rng = Application.Selection
Debug.Print "c :" & rng.Address

这将打印：

c :$B$22:$C$29

如何从第22行到第29行创建一个循环/解析开始和结束行？

并单独获取起始和结束列的int变量？

- tgkprog

Answer 1

在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

从左上角单元格开始迭代，先向右再向下。

- dspies

---

这个也可以。 - tgkprog

Answer 2

仅直接遍历行。我选择了多列，希望只遍历外层行。

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

在非连续选择中无法正常工作。使用非连续选择计数不正确，您必须使用 For Each cRow In rng.Rows 我自己没有尝试过，我的用例只涉及连续选择。谢谢 Danny Holstein（2019年评论）

- tgkprog

---

好的解决方案并且很有帮助，但需要注意它不能处理非连续选择。当处理非连续选择时，rng.Columns.Count是不正确的，你需要使用For Each cRow In rng.Rows。 - Danny Holstein