How to get a Row Number of Excel

How to get a Row Number of Excel - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: VB Scripting/Descriptive Programming (https://www.learnqtp.com/forums/Forum-VB-Scripting-Descriptive-Programming)
+--- Thread: How to get a Row Number of Excel (/Thread-How-to-get-a-Row-Number-of-Excel)

How to get a Row Number of Excel - bfakruddin - 01-09-2010

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

RE: How to get a Row Number of Excel - bfakruddin - 01-11-2010

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

RE: How to get a Row Number of Excel - Saket - 01-12-2010

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.

RE: How to get a Row Number of Excel - bfakruddin - 01-18-2010

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

RE: How to get a Row Number of Excel - sreekanth chilam - 01-19-2010

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