在VBA的帮助下将列中的连续值合并 [英] Combining consecutive values in a column with the help of VBA

问题描述

我有这样的数据:

A049
A050
A051
A053
A054
A055
A056
A062
A064
A065
A066

我想要的输出是:

如您所见，我想要连续的范围

As you can see, I want the ranges which are in consecutive order

我正在尝试类似这样的事情:

I am trying some thing like this:

Private Sub CommandButton1_Click()

    Set wb = ThisWorkbook
    lastRow = wb.Sheets("Sheet1").Range("A" & wb.Sheets("Sheet1").Rows.Count).End(xlUp).Row
    For i = 2 To lastRow
        r = wb.Sheets("Sheet1").Range("A" & i).Value

        If wb.Sheets("Sheet1").Range("A" & i).Value = wb.Sheets("Sheet1").Range("A" & i+1).Value
    Next i
End Sub

但不能帮助我

推荐答案

我感到很慈善，所以尝试了一些应该起作用的代码.假设您的起始值在A1向下，而结果在C1向下.

Am feeling charitable so have tried some code which should work. It assumes your starting values are in A1 down and puts results in C1 down.

Sub x()

Dim v1, v2(), i As Long, j As Long

v1 = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value

ReDim v2(1 To UBound(v1, 1), 1 To 2)

For i = LBound(v1, 1) To UBound(v1, 1)
    j = j + 1
    v2(j, 1) = v1(i, 1)
    If i <> UBound(v1, 1) Then
        Do While Val(Right(v1(i + 1, 1), 3)) = Val(Right(v1(i, 1), 3)) + 1
            i = i + 1
            If i = UBound(v1, 1) Then
                v2(j, 2) = v1(i, 1)
                Exit Do
            End If
        Loop
    End If
    If v1(i, 1) <> v2(j, 1) Then v2(j, 2) = v1(i, 1)
Next i

Range("C1").Resize(j, 2) = v2

End Sub

这篇关于在VBA的帮助下将列中的连续值合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！