使用VBA从多个范围创建单个范围 [英] Using VBA to create a single range from multiple ranges

问题描述

您好，这是我的第一篇文章，因为我一直都能在以前的文章中找到答案...直到现在.必须有一个帖子，但是我找不到解决我遇到的问题的帖子.我的技能水平充其量是中级:-)

Hello this is my first post as I've always been able to find my answers in previous posts... until now. There must be a post, but I couldn't find one addressing the issue I'm having. My skill level is intermediate at best :-)

我有一些表格格式的值.我想从排除一些行的范围中创建一个范围.我感觉自己与工会的关系越来越近了，但是a，走不走了.代码示例如下.结果是一个仅包含Rng1值的新范围.任何建议将不胜感激.请让我知道我是否还有其他需要提供的地方.谢谢！

I have some values in a tabular format. I want to create a range from that which excludes some rows. I felt like I was getting close with a union, but alas, no go. The code example is below. The result was a new range containing only the value of Rng1. Any suggestions would be greatly appreciated. Please let me know if I should provide anything else. Thank You!

Sub TestUnion()

    Dim Rng1 As Range, Rng2 As Range, NewRng As Range, OutputRng As Range
    
    Set Rng1 = Range("A1:D1")
    Set Rng2 = Range("A3:D5")
    Set NewRng = Union(Rng1, Rng2)
    Set OutputRng = Range("F1:I4")
    
    OutputRng.Value2 = NewRng.Value2

End Sub

推荐答案

此代码可以完成这项工作.请尝试.

This code should do the job. Please try it.

Sub TestUnion()

    ' list source ranges comma-separated
    Const Sources   As String = "A1:D1,C5,A3:D5"
    Const Target    As String = "F1"

    Dim Src()       As String               ' converted from Sources
    Dim Data        As Variant              ' value of Src(i)
    Dim i           As Long                 ' index of Src()
    Dim Ct          As Long                 ' target column
    Dim Rt          As Long                 ' target row
    
    Src = Split(Sources, ",")
    Rt = Range(Target).Row
    Ct = Range(Target).Column
    
    For i = 0 To UBound(Src)
        Data = Range(Src(i)).Value
        If InStr(Src(i), ":") Then
            Cells(Rt, Ct).Resize(UBound(Data), UBound(Data, 2)).Value = Data
            Rt = Rt + UBound(Data)
        Else
            Cells(Rt, Ct).Value = Data
            Rt = Rt + 1
        End If
    Next i
End Sub

只需在过程的顶部设置两个常量，其余代码将由代码完成.这种安排不是严格必要的，但是设置它只需要很少的时间，如果您需要进行更改，则可以节省十倍.

Just set the two constants at the top of the procedure and the code will do the rest. This arrangement isn't strictly necessary but to set it up takes very little time which will be save tenfold if you ever need to make a change.

这篇关于使用VBA从多个范围创建单个范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！