每行而不是列的每个循环非连续 [英] Non-contiguous For Each loop per row instead of column

问题描述

我有一个跨越行和列的不连续选择，并且我想对其进行For Each循环. Excel VBA通​​过首先向下循环第1列，然后向下循环2,3等来实现此目的；但我希望它先沿行循环.

I have a non-contiguous selection spanning rows and columns, and I want to do a For Each loop on it. Excel VBA does this by looping firstly down column 1, then 2,3 etc.; but I want it to loop along the row first instead.

(我的工作表看起来像下面的图片，我需要依次循环选择(版本)每列，并检索文档号和其他信息.工作表中的行数和版本列数为不固定).

(My sheet looks something like the picture below, I need to loop down the selection (version) each column in turn, and retrieve the Doc. No. and other information. The number of rows and version columns in the sheet is not fixed).

写一个相当大的Sort函数并创建一个引用数组的时间很短，我想知道是否有一种内置"方式来做到这一点?

Short of writing a fairly large Sort function and creating an array of references, I was wondering if there was a 'built-in' way to do this?

我不需要代码，只需一个解释.

I don't need code, just an explanation.

推荐答案

这是基于urdearboy的建议:

1.遍历列
2.在列中，在单元格上循环

This is based on urdearboy's suggestion:

1. loop over columns
2. within a column, loop over cells

Sub disjoint()
    Dim r As Range, rInt As Range
    Dim nLastColumn As Long
    Dim nFirstColumn As Long, msg As String
    Dim N As Long

    Set r = Range("C3,C9,E6,E13,E15,G1,G2,G3,G4")

    nFirstColumn = Columns.Count
    nLastColumn = 0
    msg = ""

    For Each rr In r
        N = rr.Column
        If N < nFirstColumn Then nFirstColumn = N
        If N > nLastColumn Then nLastColumn = N
    Next rr

    For N = nFirstColumn To nLastColumn
        Set rInt = Intersect(Columns(N), r)
        If rInt Is Nothing Then
        Else
            For Each rr In rInt
                msg = msg & vbCrLf & rr.Address(0, 0)
            Next rr
        End If
    Next N
    MsgBox msg
End Sub

这篇关于每行而不是列的每个循环非连续的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！