Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.8k views
in Technique[技术] by (71.8m points)

excel - How to create a range from two ranges in VBA?

I have two ranges, each containing a single cell (for example "A1" and "C3").

How do I get a new range containing all the cells between these two ("A1:C3")?

I tried this:

Set NewRange = Range(Range1.Address:Range2.Address)

Also how do I set a range in R1C1 format? I want to use something like Range("R1C2") instead of Range("A2").

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Like this?

Sub Sample()
    Dim rng1 As Range, rng2 As Range
    Dim NewRng As Range

    With ThisWorkbook.Sheets("Sheet1")
        Set rng1 = .Range("A1")
        Set rng2 = .Range("C3")

        Set NewRng = .Range(rng1.Address & ":" & rng2.Address)

        Debug.Print NewRng.Address
    End With
End Sub

Instead of R1C1 format use Cells(r,c). That will give you more flexibility + control

So Range("A2") can be written as Cells(2,1)


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...