# how to set Range unknown

**URL:** <https://forums.powershell.org/t/how-to-set-range-unknown/6740>\
**Category:** PowerShell Help\
**Created:** [July 1, 2016, 3:15am UTC](https://forums.powershell.org/t/how-to-set-range-unknown/6740 "2016-07-01T03:15:54Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![windows-liveuser136](https://avatars.discourse-cdn.com/v4/letter/w/3e96dc/32.png) [@windows-liveuser136](https://forums.powershell.org/u/windows-liveuser136)\
**Post date:** [July 1, 2016, 3:15am UTC](https://forums.powershell.org/t/how-to-set-range-unknown/6740/1 "2016-07-01T03:15:54Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![jonathan-warnken](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/jonathan-warnken/32/469_2.png) [@jonathan-warnken](https://forums.powershell.org/u/jonathan-warnken)\
**Post date:** [July 1, 2016, 4:49am UTC](https://forums.powershell.org/t/how-to-set-range-unknown/6740/2 "2016-07-01T04:49:43Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![windows-liveuser136](https://avatars.discourse-cdn.com/v4/letter/w/3e96dc/32.png) [@windows-liveuser136](https://forums.powershell.org/u/windows-liveuser136)\
**Post date:** [July 1, 2016, 4:51am UTC](https://forums.powershell.org/t/how-to-set-range-unknown/6740/3 "2016-07-01T04:51:11Z")

</div>

Im a beginner sorry. How would I do that

---

<div class="post-metadata">

**Author:** ![jonathan-warnken](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/jonathan-warnken/32/469_2.png) [@jonathan-warnken](https://forums.powershell.org/u/jonathan-warnken)\
**Post date:** [July 1, 2016, 5:20am UTC](https://forums.powershell.org/t/how-to-set-range-unknown/6740/4 "2016-07-01T05:20:56Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![windows-liveuser136](https://avatars.discourse-cdn.com/v4/letter/w/3e96dc/32.png) [@windows-liveuser136](https://forums.powershell.org/u/windows-liveuser136)\
**Post date:** [July 1, 2016, 6:19am UTC](https://forums.powershell.org/t/how-to-set-range-unknown/6740/5 "2016-07-01T06:19:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jonathan-warnken](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/jonathan-warnken/32/469_2.png) [@jonathan-warnken](https://forums.powershell.org/u/jonathan-warnken)\
**Post date:** [July 1, 2016, 8:45pm UTC](https://forums.powershell.org/t/how-to-set-range-unknown/6740/6 "2016-07-01T20:45:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:40pm UTC](https://forums.powershell.org/t/how-to-set-range-unknown/6740/7 "2024-05-16T20:40:58Z")

</div>