Delete the rows except particular rows

Hi all,
I have an excel sheet from which I want to empty rows except 2 rows. Capture , from the excel below I want only first and last row values rest all values to be empty. Any help would be much appreciated ! Thanks

  1. Read Range - Dt
  2. Build datatable - outputDt
  3. Add Datarow - Dt.Rows(0) pass it in datarow field.
  4. Add Datarow - Dt.Rows(Dt.count-1) pass it in datarow field.
  5. OutputDt will have only first and last row.


my proposition is to use count, if and delete rows activity :slight_smile:
First you are create additional datatable, and copy there first row and last row (based on count function).
Then you are deleting whole Excel and copy data from second datatable to excel :wink:

@kadiravan_kalidoss- i want to just empty the rows, so my final datatable should have all the Columns which are there in source datatable but the Values for first and last column should be filled , rest all the column values should be empty. Something like this

@pllo2ptk its something like below , i need output like the one which i shared,

you have mentioned about rows.

@Shailesh123 - Using a combination of read range, select range, and clear contents you should be able to achieve this.

NOTE: the ‘Get Last Row’ and ‘Get Last Column’ are part of the UiPathTeam.Excel.Extensions activity package

  1. Read range → save as dt1
  2. Get Last Row activity → save as lastRow
  3. Get Last Column activity → save as lastColumn
  4. Select Range actitivy → “B3” + LastColumn + (LastRow-1).ToString
  5. Clear contents activity → leave the ‘All’ property and the ‘Clear’ property unchecked

Hi @Dave the select range, its throwing an error Capture

Sorry i forgot the colon. Change it to “B3:” + LastColumn + (LastRow-1).ToString

@Dave - I dont want to delete Last Column values, I want to delete values in between 1st and last column. This Select range deletes the last column and first column values, I want to delete last column-1 values

@Shailesh123 - Sorry i misread that portion. In that case, you should use the post here to get the last column as a alphabetic character. First get the 2nd to last column using dt1.columns.count - 1 then use the idea from this post to convert it to the correct letter: Get last column of Excel sheet in Alphanumeric form - #7 by aksh1yadav