how to set Range unknown

This topic contains 5 replies, has 2 voices, and was last updated by Profile photo of Jonathan Warnken Jonathan Warnken 2 months, 3 weeks ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #45448

    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
    Profile photo of Jonathan Warnken
    Jonathan Warnken
    Participant

    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

    Im a beginner sorry. How would I do that

    #45465
    Profile photo of Jonathan Warnken
    Jonathan Warnken
    Participant

    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

    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
    Profile photo of Jonathan Warnken
    Jonathan Warnken
    Participant

    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)
    
Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.