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