Hi Anisha, Please find below code. Also attached is the sample Excel file used for demo in the code.
MsgBox GetTotalFilledRowsFromColumn("C:\Sample.xlsx","Sheet1",2,"B")
MsgBox GetTotalFilledRowsFromColumn("C:\Sample.xlsx","Sheet1",2,"A")
Function GetTotalFilledRowsFromColumn(
str_FileName,str_SheetName,int_StartRow,optional_ColumnName)
Dim xlApp,xlWSheet,xlWBook
Set xlApp = CreateObject("Excel.Application")
Set xlWBook = xlApp.Workbooks.Open(str_FileName)
Set xlWSheet = xlWBook.Worksheets(str_SheetName)
int_Total_Rows = xlWSheet.UsedRange.Rows.Count
int_Loop_StartValue = int_StartRow
If int_Loop_StartValue = 0 Then
int_Loop_EndValue = int_Total_Rows - 1
Else
int_Loop_EndValue = int_Total_Rows
End If
For int_xl_Row = int_Loop_StartValue To int_Loop_EndValue
If optional_ColumnName = "" Then
If IsEmpty(xlWSheet.Cells(int_xl_Row,"A").Value) = False Then
int_Total_Filled_Rows = int_Total_Filled_Rows + 1
Else
Exit For
End If
Else
If IsEmpty(xlWSheet.Cells(int_xl_Row,optional_ColumnName).Value)
= False Then
int_Total_Filled_Rows = int_Total_Filled_Rows + 1
Else
Exit For
End If
End If
Next
GetTotalFilledRowsFromColumn = int_Total_Filled_Rows
End Function
Regards
*Shalabh Dixit*My QTP Blog <http://shalabhdixit.wordpress.com/>
On Mon, Jun 1, 2015 at 9:38 AM, <[email protected]> wrote:
> How to Find numbers of Rows for specific column in Excel File.I have used
> Usedrange.Rows.count but it will give you maximum number of rows but I want
> number of rows for specific column in Excel File
>
> Set xl =CreateObject("Excel.Application")
> Set wb = xl.Workbooks.Open ("Test Plan.xlsx")
> Set ws = wb.worksheets(1)
> 'rc= ws.UsedRange.Rows.count
> '
> 'msgbox rc
>
> --
> --
> You received this message because you are subscribed to the Google
> "QTP - HP Quick Test Professional - Automated Software Testing"
> group.
> To post to this group, send email to [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> For more options, visit this group at
> http://groups.google.com/group/MercuryQTP?hl=en
>
> ---
> You received this message because you are subscribed to the Google Groups
> "QTP - HP Quick Test Professional - Automated Software Testing" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
--
You received this message because you are subscribed to the Google
"QTP - HP Quick Test Professional - Automated Software Testing"
group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/MercuryQTP?hl=en
---
You received this message because you are subscribed to the Google Groups "QTP
- HP Quick Test Professional - Automated Software Testing" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
Sample.xlsx
Description: MS-Excel 2007 spreadsheet
