title: Get the Number of Rows in Column of a worksheet
body: <p>I have an Excel sheet as follows:</p>
  
  <pre><code>Id   Name   Address
  -------------------
  1    t1     Add1
  2           Add2
  3
  </code></pre>
  
  <p>I want to get the count of non-empty cells in each column, i.e.:</p>
  
  <blockquote>
    <p>Column ID &rarr; 3 rows<br>
    Name &rarr; 1 row<br>
    Address &rarr; 2 rows</p>
  </blockquote>
  
  <p>How do I do this in power shell? I tried various things but they don't seem to work.</p>
  
  <p>I am new to PowerShell. Is there a way to get the number of rows for a column directly?</p>
  
  <pre><code>$SheetName = "Sheet1"
  $Excel = New-Object -ComObject "Excel.Application"
  
  $Workbook = $Excel.workbooks.open($XLSDoc)
  $Sheet = $Workbook.Worksheets.Item($SheetName)
  $objRange = $Sheet.UsedRange
  $RowCount = $objRange.Rows.Count
  $ColumnCount = $objRange.Columns.Count
  Write-Host "RowCount:" $RowCount
  Write-Host "ColumnCount" $ColumnCount
  $Range1 = $Sheet.Range("A1:A5")
  $TestCount = $Range1.Rows.Count
  Write-Host $TestCount
  </code></pre>