how to set Range unknown

PowerShell Help
1

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?

2

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.

3

Im a beginner sorry. How would I do that

4

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.

5

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)
6

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)