Excel VBA:选定的单元格循环 [英] excel vba : selected cells loop

问题描述

想要在excel VBA中迭代选择中的每一行.

Would like to iterate each row in a selection in excel VBA.

我有:

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

此打印

c:$ B $ 22:$ C $ 29

c :$B$22:$C$29

如何使第22行到29/的循环循环解析出起始行和结束行?

How to make a loop from row 22 to 29/ parse out the start and end row?

然后在单独的int变量中获取开始和结束列?

And in separate int variables get the start and end column?

推荐答案

仅直接在行上进行迭代.我选择了多个列，只希望外循环行.

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

不适用于非连续选择.非连续计数不正确，您必须在rng.Rows中使用For Each cRow.我自己没有尝试过此操作，我的用例仅用于连续.谢谢丹尼·霍尔斯坦(Danny Holstein)(2019年评论)

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)

这篇关于Excel VBA:选定的单元格循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！