excel问题，如何在一列数据中筛选出连续数字

发布网友 发布时间：2022-04-22 07:24

我来回答

共1个回答

热心网友 时间：2022-06-17 15:53

用VBA写代码
注意：只能在C1单元格输入，查找对象从A3单元格开始，将代码复制到当前工作表
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("c1")) Is Nothing Then
If Range("c1") = "" Then Exit Sub
Dim arr(1 To 100) As String
Dim str1, str2 As String
Dim i, r, l, n, s As Integer
Range("a2:a10000").Interior.Color = xlNone
str1 = Range("c1")
l = Len(str1)
r = Application.CountA(Range("a3:a10000"))
For i = 1 To r - l + 1
str2 = ""
For s = i To i + l - 1
str2 = str2 & Range("a" & s + 2)
Next
arr(i) = str2
Next
On Error GoTo Err_Handle:
n = WorksheetFunction.Match(CStr(str1), arr, False)
Range("a" & n + 2 & ":a" & n + 1 + l).Interior.Color = vbRed
Exit Sub
Err_Handle: MsgBox "没有查询到有效数据"
End If
End Sub