How can I count the number of rows in an excel sheet column...

Hi anusdesai,

Try this function....

    Public Function GetColumnRowCount(ByVal col As Integer, ByVal xlWorksheet As Object)
        Return xlWorksheet.Application.Intersect(xlWorksheet.UsedRange, xlWorksheet.Columns(col)).Rows.Count
    End Function

So you need to pass 2 arguments. The column "col", and the Excel Worksheet object, which is the worksheet containing the column you need to retrieve the row count from. An example, using the declarations from your previous question....

Dim RowCount As Integer = GetColumnRowCount(8, xlWS)

NOTE: The above function also counts blank cells. It returns the number of rows from the first to the last value in that column. To count only those cells with values, use this function....

    Public Function GetColumnRowCount(ByVal col As Integer, ByVal xlWorksheet As Object)
        Return xlWorksheet.Application.WorksheetFunction.CountA(xlWorksheet.Columns(col))
    End Function

Regards,

Wayne

Hi,
Public Function unusedexcel(ByVal workbook As String, ByVal ExcelSheetName As String, ByVal row As Integer, ByVal col As Integer)


        'Dim SpreadSheetProfund As String
        'SpreadSheetProfund = "J:\Implementation\PHT_vss\RegularInterface\Files\Personal_Details.xls"
        Try
            Dim xlApp As Object = CreateObject("Excel.Application")
            Dim xlWB As Object = xlApp.Workbooks.Open(workbook)

            Dim xlWS As Object = xlWB.Worksheets(ExcelSheetName) ' change the name of the sheet you require

         
            GetColumnRowCount(1, xlWS)-------your function

            xlWB.Close()
            xlApp.Quit()
            xlWS = Nothing
            xlWB = Nothing
            xlApp = Nothing


i get get erro type mismatch


        Catch ex As Exception
            MsgBox(ex.ToString)
            AddMessage("Spread sheet record count - " & FormatMessage(12, Err.Number, Err.Description))
            AddMessage("Error processing 'Count Rows', please contact your consultant")
        End Try

    End Function

where abouts does the error occur?

Also, you're not using the value returned by the function. It meant to be used like this....

    Dim RowCount As Integer = GetColumnRowCount(8, xlWS)

Wayne

also, which function are you using?

oki will try and be back

your function:
  Public Function GetColumnRowCount(ByVal col As Integer, ByVal xlWorksheet As Object)
        Try

            Return xlWorksheet.Application.Intersect(xlWorksheet.UsedRange, xlWorksheet.Columns(col)).Rows.Count
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Function


Same error type mismatch...com exception...

are you sure the exception is originating from the function?

Yup!!

Again Amazing!!

Cheers mate

Hi Wayne,

I need one more help in excel...see my next question ..

I will be great if you help me in that....

It will be great..sorry for the spelling mistake