Hi, I have a requirement where I have an excel file in these I have multiple sheets.
Now my requirement is for particular sheet suppose sheet1.
In this sheet I have multiple columns like Date | Type | Destination | Arrival time and so on
so these Rows is in 11th Rows.
I have to select these complete row Then click on Wrap Data(Optional if there is an shortcut we can use) Then I have to click on Data Tab the Filter. Once click on filter I have to filter the Type Column then select specific item from this lets say Order, PurchaseDate once done with the filter I have to delete the data from the rows 12th till end.
We can do this with Macros
—record a macro that records all the above movements in the excel and save that macro in a text file and use Invoke vba or execute macro inside excel application scope to execute that macro
Hope this would help you
Kindly try this and let know for any queries or clarification
Cheers
I have no idea about macro. I didn’t use it before bro.
So can you please tell me how to record and use in macro.
And what are the steps I have to follow to achieve this. @Palaniyappan Thanks for your quick response.
I am selecting complete row 11th no matter how many column are there once select the complete row I have to click on Wrap Data which is in Home Tab. Then Click on Data Tab and click on Filter once filter enable I have to bring my cursor on this right side srop down icon and press Left click on Type Column and marked the Order then press Ok
Then Select from the 12th rows and till end delete all columns data.
I think it’s possible by Macro.
@Palaniyappan But our last row of the data is dynamic can we make it dynamic using Macro. Please suggest me to use Macro here. This is a gold chance for me to learn macro here…
Oh, You are trying to make changes in the pivot table @balkishan. I hope you are working on a regular data table or data .
Just go to developer tab in the excel header and click on record macro. Then start doing all the things you want to do and once you are done with all the things, click stop recording. Then save the macro . So you can use it in Invoke VBA activity to do the things
@Palaniyappan@HareeshMR
sorry for the inconvenience, Just little change in the requirement. Note. Now I am not selecting 11th row and click on Data Wrap and select Data and Filter it.
Now I have to direct select from the 12th row onward and select the complete data till end and delete it. So for this I have to use Macro or I can use some other query to delete data from 12th row till end.
Any shortcut is there for this…
I am little worry that Macro will select 12th row every time and select the complete data till end and delete it. Some other method please suggest me
Nothing to do @balkishan. Just save that macro to a known location outside and whenever a new file comes, after opening it in a excel application scope, use INVOKE VBA activity and provide the path of Macro file. But you have to change the range and column numbers to dynamic so that it will work if the range changes
Even it is a pivot table, we can select and clear the contents in the excel. But we are not able to do that in existing activities, you have use Invoke code activity to clear the contents in the excel.
Here it’s showing me number of rows bro. But i know the 12th row but don’t know how many rows are there in the sheet it’s always varies.
As I told you it’s huge data. So I want to delete from 12th row till end of the data…
Yes @balkishan,
Use read range to get the row count as datatable.rows.count and as you are deleting from 12 row, the number of rows you want to give will be the totalCount-11
Before using Insert/Delete rows activity, use excel application scope and a read range activity and give that sheet name. Store the data in a data table. Then in the assign, create a variable of type count and get the count of rows in excel using datatable.rows.count.
Then use the Insert/Delete activity so that the number of rows you want to delete will be the count variable you have - 11. and the position is from 12 @balkishan
you were almost done
–Fine lets do one this
–use a read range activity and mention the range as “A12” and we will be getting the output with a variable of type datatable named outdt
–now use a insert/delete rows activity and in position mention as 12 while in NoRows mention as outdt.Rows.Count
Simple isn’t it
Kindly try this and let know for any queries or clarification
and marked the Order 
then press Ok
.
