How to get a Row Number of Excel

[bfakruddin]

Hi,

How to get a Row Number of a Row in an excel sheet.

I don't want to loop for all rows... I need to get one row, I don't know that row no. I wanna get the row number by using a value in any cell in that row.

Thanks & regards,
Baba Fakruddin.D

[bfakruddin]

Hey Folks,

How r u?

Hi Ankur, I am waiting for your reply. Have a look at my question.

Please reply me as early as possible on my issue.

i.e., I set a Filter to a Excel Column and Filtered my desired value, Now I need to run a loop for the filtered rows, not for all the rows contained in a excel sheet.

Please help me out.

Thanks & regards
Baba Fakruddin.D

[Saket]

You can try something like

Code:

xlCellTypeVisible =12
set oExcel  = createobject("excel.application")
oExcel.visible = true
oExcel.Workbooks.Open ("D:\My QTP\Excel Issue\AFIFLONG\AFIFLONG.xls")
Set sRange = oExcel.Range("A2", "A15").SpecialCells(xlCellTypeVisible)
For each cell in sRange
    msgbox cell.address
Next

I have concidered only the first column here which is already filtered. and assumed the first row is your header row and 15th row is the last used row in your excel
hope this will helps you.

[bfakruddin]

Thank You Mr. Saket,

Extension for my previous Question.

I filtered a column with my desired value and I got some rows with different row Numbers (not serially). Now I need to get a row count of the filtered sheet and run a loop for these rows which I got in sheet.

Please help me out as early as possible.

Thanks & Regards,
Baba Fakruddin.D

[sreekanth chilam]

Hi Baba,

Try with the below code :

Code:

xlCellTypeVisible =12
set oExcel  = createobject("Excel.Application")
oExcel.visible = true
oExcel.Workbooks.Open ("C:\..\..\..\sample.xls")
Set sRange = oExcel.Range("C1", "C99").SpecialCells(xlCellTypeVisible)
temp_row=0
  For Each cell in sRange
        temp_row=temp_row+1
             If temp_row = 1 Then
                  Print "#####Column Header Row#####"
    Else
         d=split(cell.Address,"$")
         print "Filtered row # "&temp_row-1&" : "&Cell.Value &" and its Excel Row# : "&d(2)
            End If
  Next
  Filtered_Rowcount=temp_row-1
  Print "Numer of Filtered rows:"&Filtered_Rowcount
oExcel.ActiveWorkBook.Save
Set oExcel=Nothing

Note : You can also know the filetered rows count using below way too.

Code:

Msgbox sRange.Count-1