CODEWITHSUNDEEP
vbaexcel
Mehmood Hassan
Mehmood Hassan

Reputation: 111

returning row number only in vba

In excel vba is there way for getting only row number from range.

 set range=selection

Selection can be complete multiple row consecutive on or non consecutive or only cells it depend on user before running micro. following line can give me row number but like this it will repeat the row for each cell but I want only row number only one time from any number of cells selected in row. 

for each cell in range

    cell.row

next cell

or 

for each range in SelectedRange.rows

    range.row

next range

Upvotes: 0

Views: 6455

Answers (3)

Vasily
Vasily

Reputation: 5782

another one variant using dictionary:

Sub dural2()
   Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")
   Dim r As Range
   dic.comparemode = vbTextCompare
   For Each r In Selection
       If Not dic.exists(r.Row) Then dic.Add r.Row, ""
   Next r
   MsgBox Join(dic.keys, Chr(10))
End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

This will report each row only once:

Sub dural()
   Dim c As Collection
   Set c = New Collection
   On Error Resume Next
      For Each r In Selection
         c.Add r.Row, CStr(r.Row)
      Next r
   On Error GoTo 0

   msg = ""

   For i = 1 To c.Count
      msg = msg & vbCrLf & c.Item(i)
   Next i
   MsgBox msg
End Sub

For example:

enter image description here

Upvotes: 1

Fadi
Fadi

Reputation: 3322

Try this code:

Sub test()
 Dim Rng As Range, CL As Range

 Set Rng = Selection

 For Each CL In Application.Intersect(Rng.EntireRow, Rng.Worksheet.Columns(1))
  Debug.Print CL.Row
 Next
End Sub

Note: you can use any column 2,3,4.....

Upvotes: 1

Related Questions