如何获取起始行号与结束行号

6739912

根据A列数值不同获取起始行号与结束行号

chentonny

r1 = Columns(1).Find("乙", searchdirection:=xlNext).Row
r2 = Columns(1).Find("乙", searchdirection:=xlPrevious).Row

hzruziniu

1. Private Sub Worksheet_Change(ByVal Target As Range) '下拉或单元格值变化事件
   
2.     Dim s$, r&, r2&
   
3.     If Target.Address = "$G$3" Then
   
4.         On Error Resume Next
   
5.         s = Range("g3")
   
6.         r = [a:a].Find(s, , , 1).Row
   
7.         r2 = [a:a].Find(s, , , , , 2).Row
   
8.         MsgBox "“" & s & "”出现的第1个行号为：" & r & Chr(10) & "“" & s & "”出现的最后一个行号为：" & r2
   
9.     End If
   
10. End Sub
    
11. Private Sub Worksheet_Activate()
    
12.     Dim d, i&, arr
    
13.     Set d = CreateObject("Scripting.Dictionary")
    
14.     Set Sht1 = Sheet1
    
15.     Myr = Sht1.[a65536].End(xlUp).Row
    
16.     arr = Sht1.Range("a2:b" & Myr)
    
17.     For i = 1 To UBound(arr)
    
18.         d(arr(i, 1)) = ""
    
19.     Next
    
20.     With Range("g3").Validation
    
21.         .Delete
    
22.         .Add 3, 1, 1, Join(d.keys, ",")
    
23.     End With
    
24. End Sub
    

复制代码

下拉G3单元格的字典值
如何获取起始行号与结束行号.rar (14.98 KB, 下载次数: 29)

2019-1-20 13:47 上传

点击文件名下载附件

6739912

谢谢回复.......