how to set Range unknown

Welcome Forums General PowerShell Q&A how to set Range unknown

This topic contains 5 replies, has 2 voices, and was last updated by

 
Participant
2 years, 4 months ago.

  • Author
    Posts
  • #45448

    Participant
    Points: 0
    Rank: Member

    I have a spread sheet which I'm extracting data from. The spread sheet has a load of useless lines so I start at A5. Columns go through to Q and currently data ends at row 24 thus I have
    $Range = $WorkSheet.Range("a5","q24")
    But if some one adds a line to the spread sheet I am sung so how do I select the range from being A5 TO Q(whatever the last row with data is?

  • #45459

    Participant
    Points: 0
    Rank: Member

    You would need to have your script inspect the spreadsheet and based on the value of the data in the cells determine where to end your range.

  • #45461

    Participant
    Points: 0
    Rank: Member

    Im a beginner sorry. How would I do that

  • #45465

    Participant
    Points: 0
    Rank: Member

    If the key value to determine if the row has usable data is in column A you would do something like this.

    $row = 5 
    do{
      If($WorkSheet.ActiveSheet.Cells.Item($row,1).Text -ne "Awesome Text"){$endofrange = $row}
      $row++
    }until($endofrange)
    

    This will start and A5 and keep checking until "Awesome Text" is not found and and give you a variable that tells you the 1st row that it was not found.

  • #45469

    Participant
    Points: 0
    Rank: Member

    Thanks for you help but as I say I'm a beginner. I have 2 issues with that from my limited understanding, I don't know what any of the text will be so is there anything for any text? Secondly if there's a way to do that how do fit it into my script as it stands now

    $Workbook = $Excel.Workbooks.Open("a.xlsx")
    $WorkSheet = $WorkBook.Worksheets.Item("Live test  and training")
    $WorkSheet.activate() 
    $Range = $WorkSheet.Range("a5","q24")
    $Range.Copy()
    $Worksheet = $Workbook.Worksheets.item(2)
    $Range = $Worksheet.Range("a1")
    $Worksheet.Paste($range)
  • #45563

    Participant
    Points: 0
    Rank: Member

    Will you always extract every row starting at row 5?
    Will the number of columns stay the same or will it change?

    I am going to give you an example assuming you will take any row with data in column A and that the data will always be in columns A-Q

    $Workbook = $Excel.Workbooks.Open("a.xlsx")
    $WorkSheet = $WorkBook.Worksheets.Item("Live test  and training")
    $WorkSheet.activate()
    $row = 5
    Remove-Variable endofrange # this is here for testing you should not need this in the final script
    do{
      If($Workbook.ActiveSheet.Cells.Item($row,1).Text -eq ""){$endofrange = $row}
      $row++
    }until($endofrange)
    $endofrange = $endofrange - 1
    $Range = $WorkSheet.Range("a5","q$endofrange")
    $Range.Copy()
    $Worksheet = $Workbook.Worksheets.item(2)
    $Range = $Worksheet.Range("a1")
    $Worksheet.Paste($range)
    

The topic ‘how to set Range unknown’ is closed to new replies.