Search code examples
excelpowershellrpa

Selecting cell range from Excel using Powershell

I was trying to select from A55 to the last row that is filled from column A in Excel using Powershell script.

My first script worked perfectly which is:

$lastRow = $ivWorksheet.Cells.Item($ivWorksheet.Rows.Count, "A").End(3).Row
$ivRange = $ivWorksheet.Range("A55:A$lastRow")

However when I changed above into below, it was not working.

$startRow = 55
$lastRow = $ivWorksheet.Cells.Item($ivWorksheet.Rows.Count, "A").End(3).Row
$ivRange = $ivWorksheet.Range("A$startRow:A$lastRow")

I was expecting both script to have same result, but the latter one did not work. Could you please advise what the reason is?

Solution

  • $startRow:A is being interpreted as a variable in your second attempt and because that variable isn't defined, it is expanded to null:

    $startRow = 55; $lastRow = 123
"A$startRow:A$lastRow" # => A123

    You need to use ${ } to delimit it, in other words, to indicate where the variable starts and where it ends so PowerShell can resolve it correctly. See Variable names that include special characters for more information.

    $startRow = 55; $lastRow = 123
"A${startRow}:A$lastRow" # => A55:A123