topic Retrieving the last row of an Excel spreadsheet to delete when the row is dynamic in Product Forum

Retrieving the last row of an Excel spreadsheet to delete when the row is dynamic

AngelaBarnes — Wed, 11 Aug 2021 13:15:00 GMT

I am trying to retrieve the last row of an Excel spreadsheet (which the last row is dynamic) in order to delete it then save the file. I used an Excel VBO Business Object and was able to count the number of rows and output that number into a data item (to be used to identify the last row of the spreadsheet). However when I run the process and it gets to the step where I have an action that uses the Excel VBO Business Object with the Action "Select", and reference the previously created data item as the Cell Reference value, its erroring (Exception: Failed to select row: Exception from HRESULT:0x800A03EC). I entered the Value of the Cell Reference as "[Number of Rows]:[Number of Rows]". I also tried using "[Number of Rows]". Got the same error. Since the Cell Reference has to be of type Text, I changed the data item's type to Text but still getting the same error. If I input numbers as the Cell Reference instead (Ex. "1:5"), it works. Is there a simple fix for this?

------------------------------
Angela Barnes
------------------------------

RE: Retrieving the last row of an Excel spreadsheet to delete when the row is dynamic

michaeloneil — Wed, 11 Aug 2021 13:46:00 GMT

Hi Angela

The number of rows is returning the count but no column number or letter you can use the count to select the cell as long as you know the column of the cell you want to delete the value from. For example if the column you want to select is A and the row count is 27 then in the select action enter "A"&[Number of Rows] and this selects the cell A27. Similarly if you need to select a range of rows and you know the start and end columns then enter "A"&[Number of Rows]&":G"&[Number of Rows] which would be A27:G27.

If you would rather you got the cell reference then you can use the action Find Next empty cell and this will get the last empty cell and you would need to do an calculation to amend the cell reference to reduce the rows by 1. If you have a lot of rows though this action can be very slow.

Alternatively if you are ok with coding in VB then there are a couple of options you can create a new action to get the last row with value, example code is below:

Dim wb, ws As Object
Dim excel, sheet, range As Object

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(StartCell)
range.Select()
range.End(direction).Activate()

Success = True
cellref = excel.ActiveCell.Address(false, false)

Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
ws = Nothing
excel = Nothing
sheet = Nothing
range = Nothing
End Try

Inputs will be Handle, Workbook, Worksheet, direction and starting cell. Direction you will need to set as DOWN, UP, LEFT or RIGHT and you will need to use a calculation stage prior to the code stage and set the enumerator depending on the which input direction was given.

A simpler code stage would be to delete the entire row you want to remove. The code is below:
Dim wb, ws As Object
Dim strDir as String = direction.Trim().Substring(0,1).ToUpper()
Dim dirn as Integer = 0
Dim excel, sheet, range As Object

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(Reference)
range.Select()

Select Case strDir
Case "U"
dirn = -4162 ' Excel.XlDirection.xlToUp
Case "L"
dirn = -4159 ' Excel.XlDirection.xlToLeft
Case "R"
dirn = -4161 ' Excel.XlDirection.xlToRight
Case Else
Throw New ArgumentException("Invalid Direction: " & strDir)
End Select

range.Delete(Shift:=dirn)

'If direction = "UP"
'range.Delete(Shift:=-4162)
'else range.Delete(Shift:=-4159)
'end if

The inputs for this code stage and the action are the Handle, Workbook, Worksheet and Reference. The reference is the row range which after using the action count rows you can use the data item to delete this row and you should just enter this as [Number of Rows]&":"&[Number of Rows]

Hopefully this makes sense.

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

RE: Retrieving the last row of an Excel spreadsheet to delete when the row is dynamic

ritansh.jatwani — Wed, 11 Aug 2021 19:46:00 GMT

Hi @AngelaBarnes,

The simplest solution would be to use the below expression in the select action of MS Excel VBO:

[Number of Rows] & ":" & [Number of Rows]

Please use the correct syntax and you will be able to delete the last row. Hope it helps !!

------------------------------
Ritansh Jatwani Senior Consultant
Consultant
EY
Gurgaon
*If you find this post helpful mark it as best answer
------------------------------