[RESOLVED] How to loop all used cells certain column in a sheet vb.

[Peter Swinkels]

Add a COM reference to "Microsoft Excel version Object Library" to your project and use this code:

Option Compare Binary
Option Explicit On
Option Infer Off
Option Strict On

Imports Microsoft.Office.Interop
Imports System

Public Module Module1
   Public Sub Main()
      Dim xlApp As New Excel.Application With {.DisplayAlerts = False}
      Dim xlSht As Excel.Worksheet = Nothing
      Dim xlRng As Excel.Range = Nothing

      xlApp.Workbooks.Open("D:\Test1.xls")
      xlSht = DirectCast(xlApp.Sheets(1), Excel.Worksheet)
      xlRng = DirectCast(xlSht.Cells(1, 1), Excel.Range)
      Console.WriteLine(xlRng.Value)

      For i As Integer = 1 To DirectCast(xlApp.ActiveSheet, Excel.Worksheet).UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row
         Console.WriteLine(DirectCast(xlSht.Cells(i, "A"), Excel.Range).Value)
      Next i

      Excel.Quit()
   End Sub
End Module