none
Find the extend of a table in excel RRS feed

  • Question

  • If I have got a table from B2:D500

    Assumming keeping the start cell, and don't know the end row and column. how to find the last cell of the table ?

    Tuesday, October 14, 2014 3:18 AM

All replies

  • anyone has idea ?

    Tuesday, October 14, 2014 6:59 AM
  • hello everybody.. Any idea ?
    Wednesday, October 15, 2014 2:16 AM
  • ###############IDENTIFY the range of table ###########################
    function getrangeoftable {

    param (

    [string]$starting

    )
    $start=$starting
    $startcell=$ws.range($start)
    $startrow=$startcell.Row
    $startcolumn=$startcell.Column
    #$startrow
    #$startcolumn

    ######find end ROW#############
    DO
    {
     $value=$ws.cells.item($startrow,$startcolumn).text
     $startrow++

    } Until ($value.Length -eq 0)
    $endrow=$startrow-2
    #$endrow

    #############find end column###################
    $startcell=$ws.range($start)
    $startrow=$startcell.Row
    $startcolumn=$startcell.Column
    #$startrow
    #$startcolumn
    DO
    {
     $value=$ws.cells.item($startrow,$startcolumn).text
     $startcolumn++

    } Until ($value.Length -eq 0)
    $endcolumn=$startcolumn-2
    #$endcolumn

    Function Convert-NumberToA1 {
      <#
      .SYNOPSIS
      This converts any integer into A1 format.
      .DESCRIPTION
      See synopsis.
      .PARAMETER number
      Any number between 1 and 2147483647
      #>
      
      Param([parameter(Mandatory=$true)]
            [int]$number)
     
      $a1Value = $null
      While ($number -gt 0) {
        $multiplier = [int][system.math]::Floor(($number / 26))
        $charNumber = $number - ($multiplier * 26)
        If ($charNumber -eq 0) { $multiplier-- ; $charNumber = 26 }
        $a1Value = [char]($charNumber + 64) + $a1Value
        $number = $multiplier
      }
      Return $a1Value
    }
    $endcolumnletter=Convert-NumberToA1 -number $endcolumn

    $endcell=$endcolumnletter+$endrow
    $tablerange=$ws.range($start,$endcell)
    $tablerange.copy()
    $copyrange=$ws.range("G1")
    $copyrange.Pastespecial(-4163)


    }


    getrangeoftable -starting "G16"

    Thursday, October 16, 2014 1:46 AM