Hey, I have a scenario where i have two sheets.
Sheet A has few columns and rows, so I compare one column vale from sheet B.
If the number found in input sheet, I will update the column of sheet B. After updating whatever the rows has matched from Sheet A and which I copied to Sheet B. I want them to delete from Sheet A.
Any suggestion please do let me know. I can not use copy sheet as there are formula and structure so. As of now I am using delete range but its not working. So i want when I copy the rows from sheet A to B b by filtering data tabke at the same moment it should deleet thows rows
You could do this with Data Table inner join operations, combined with Excel UI functionality to Delete a row
First you load both sheets into two Data Tables dtA and dtB
You use Data table Inner Join on the key you need to identify the intersects between dtA and dtB
Loop through matched rows and update Sheet B using Write Cell
Make sure to capture the position of the Row in Sheet A and add it to a collection. You will need this for the delete operation. Example: If you loaded data with Add Headers options, then your first row in the DataTable actually starts at A2.
Switch to Sheet A
For each Row to Delete in Collection from above:
To Select Row, use UI Automation:
- CTRL+g to activiate GoTo dialog
- Type in your row - Example: to delete 14th row, the cell number is A15
- Click OK to position cursor to Row A15 and dismiss GoTo dialog
To Delete Row, use Hotkeys
- Hotkey ALT+h to activate Home menu on top ribbon
- Hotkey d to select Delete
Hotkey r to delete current row A15
No I need to delete through script, not by opening it.
I want dynamic something
You need to explore Microsoft Interop or any of the Excel libraries to do it.
There is an activity known as Insert/Delete Rows
So if you’re running a loop you can use this activity at the end when match is found and delete the row.
Check it out if it helps.
Hi I did its changing format of that sheet so can not use it, So I used the delete range and by using rows count I delete and and then fiktered rows I write it. Thanks for the suggestion
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.