Deleting excel cell values of a particular column and update the same column with New set of values

Help
1

Team,

I am working on a project which requires deletion of excel values of a particular column and update with new value. Basically its similar what we do manually in excel by finding an existing value and replace all with a new value. So what I need is how can I replace with the new value coz I dont want to search for the existing value as I want to delete the cell values of a particular column (Delete) and paste the new value in all the cells where the old data has been deleted from. But I have to keep track of the number of values that it will delete , should match with the no of pasting of the new value. This is something I need as the excel contains multiple table data , values of a particular table , separated by a blank row. so once the blank cell reaches, it should stop deleting and paste the new value exactly the same no of times that it has deleted from the column. Please do let me know of any clue or in case you have any doubt of my requirement.

Thanks.

2

Table data will look like :slight_smile:
imageTable 1- Emp
name hiredate recordno
Anup1 4/8/2017 123
Anup1 4/9/2017 123
Anup1 4/10/2017123
[Blank row]
Table 2- Emp1
name hiredate recordno
Anup1 4/8/2017 123
Anup1 4/9/2017 123
Anup1 4/10/2017123

So my column name is fixed which needs replacement of values and I know it. Les say its RecordNo Column as above. It will automatically check in the whole excel and will delete 123 from that column for both emp and emp 1 and replace with a new value , say 456 in both the tables. So checking the Blank row is also important so that it does not mix up.
The final data should look like :slight_smile:

imageTable 1- Emp
name hiredate recordno
Anup1 4/8/2017 456
Anup1 4/9/2017 456
Anup1 4/10/2017456

Table 2- Emp1
name hiredate recordno
Anup1 4/8/2017 456
Anup1 4/9/2017 456
Anup1 4/10/2017456

Thanks…

3

Please ignore the excel snaps as the 2nd figure is not updated with value 456, so mentioned the expected data set below the figure.