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/parse的开始行和结束行？

在单独的int变量中获取启动和结尾列？

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

从最左上的大多数小区迭代，然后向下。

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

它不适用于非连续选择。计数对于不合格是不正确的，您必须在rng中的每只乌鸦中使用。行我自己没有尝试过，我的用例仅适用于连续。谢谢Danny Holstein（2019年评论）