lastUsedRow in ReadRange activity logic

[alex_never]

Is it possible to limit the definition of lastUsedRow to the specified Cells. For example, in the Column A there is a list of dates for the full year, in the Column B data values are added on a weekly basis - on the monday for the previous week. Is it possible to Get the value of the lastUsedRow for Column B specifically, to automate the insertion of new data. So far, the lastUsedRow is defined by the Column A in my example, even if the Cells are specified as “B:B”.

[Allan_Zimmermann]

right now, it finds lastUsedRow and lastUsedColumn based of UsedRange for the sheet.

I could see why it would make more sense to use the range for those.
I think this code seem to get it, but I cannot test it, my office is now refusing to run without me buying office. I’m pretty sure office used to allow me to use it, but showing a nag screen about being unregistered. I need to see if i can find someone who wants to sponsor that, before i can do more here.

[alex_never]

Find the way to do it with the help of Invoke code and power shell script: finds the last row for the column B and returns the value from the same row from column A.

# Create an instance of Excel
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

# Open the workbook
$workbook = $excel.Workbooks.Open($report)

# Select the first worksheet
$worksheet = $workbook.Sheets.Item(1)

# Find the last used row in column B
$lastRow = $worksheet.Cells($worksheet.Rows.Count, 2).End(-4162).Row

# Get the value in column A of the last used row
$lastRowValue = $worksheet.Cells($lastRow, 1).Value()

# Output the results
# Write-Output "The last used row in column B is: $lastRow"
# Write-Output "The value in column A of the last used row is: $lastRowValue"

# Clean up
$workbook.Close($false)
$excel.Quit()

# Release COM objects
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheet) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

ModEdit: added code tags around the script