Delete shift cells up in excel but need to do in studio x

Help StudioX
,
1

hi, how to delete specifi row and column using studio x? i have try to indicate in excel but fail. my problem is i want to delete row 1 in column j

2

Hi, here is a suggestion on how to solve your problem :slight_smile:

Explanation:
If you want to delete the entire row or entire column you can use “Delete Rows” and “Delete Column” activities and indicate row index or column name

image
image546Ă—768 19.7 KB

**Note that it will delete entire row or column
image
image848Ă—103 2.22 KB

But per my understanding you are trying to delete only cell J1 and shift the remaining cells up in that column.

Solution I would use (it needs a little bit of logic since there is no “shift cell up” activity")

  • Use “Clear Sheet/Range/Table” activity to clear the J1 cell
  • Read the remaining cells above using “Read Range” activity (only read column J starting from cell J2) and store it in a variable
  • Clear the entire column J using again “Clear Sheet/Range/Table” activity
  • Paste the values stored in the variable to the column J again, this will shift the original content up

image
image566Ă—825 21.7 KB

Results will be from a table like this:

image
image879Ă—187 2.85 KB

To a table like this:

image
image872Ă—199 2.7 KB

I have attached the UiPath project in case you want to test it :slight_smile:
Deleting_Columns_Rows.zip (66.8 KB)

1 Like
3

hi, first of all thanks you for the step appreciate it. may i ask, i already use activity write data table to excel beofre as my data source is from tex file. do i can repeat as per your working activity write data table to excel? i have try but it cannot running

4

Hi, per my understanding you are reading the content from a text file and then using “write data table” to generate this excel file, in that case, after generating the excel file you will need to use “read range” only in the column J (like my example) to generate another data table specifically for that column J (and the datatable variable should be a different name to avoid confusion and overwrite data from the wrong datatable) and then write this range in the column J. Not sure if that was your doubt, feel free to share more information and I can try to help :slight_smile:

5

Hello @Nur_Amin

Excel Application Scope
Read Range (Output: DataTable)
Remove Data Row (Input: DataTable, RowIndex: 0) // Delete row 1
Remove Data Column (Input: DataTable, ColumnName: “J”) // Delete column J
Write Range (Input: Updated DataTable)

Thanks & Cheers!!!

6

Hi, may i ask also, let say i have 3 rows that need to be shift cells up. did i need to repeat as per your worksheet (clear sheet, read range, clear sheet,write data table) 3 times?

7

can help me with the worksheet?