Excel Query Data Remove

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.

Any way to do this please suggest.

@Palaniyappan @HareeshMR @lakshman Please help.

Sample
image

This is just an sample but in real my data is huge So I have to filter and delete all rows data from 12th row till end

@Jan_Brian_Despi

2 Likes

Fine

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

1 Like

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.

It was mentioned like excel and so suggested macro

Are we getting this from CSV
Cheers @balkishan

1 Like

No, this is in .xlsm format bro
image

so can we use macro here, sorry by mistake I mentioned .csv format

1 Like

Yah that’s possible
It’s a macro enabled file only
@balkishan

1 Like

@balkishan,

You can read the CSV or xlsm or whatever it is by mentioning the range.

  1. Use read range and read the entire data and get the range of the excel using datatable.rows.count and assign to variable.
  2. Use read range activity and mention the range as “A12:ZZ” + count.tostring (ZZ I gave as optional. mention range you have) and store in datatable
  3. Then use filter datatable activity to filter on column

Bro, did you understand my issue.

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 image and marked the Order image 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 :slight_smile:.

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

1 Like

Yes I am doing in that way bro. Is there any specific thing here to select after clicking on Macro?
image

I have to do every time this activity whenever new file will come. Will this Macro work for All excel with the same format. ?

@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

@lakshman @Jan_Brian_Despi @Lahiru.Fernando @KarthikByggari

1 Like

@HareeshMR are you there

Fine
In that case we can use INSERT/DELETE ROWS activity


Where we can mention the no of rows and the position from where we want to delete

Hope this would help you
Kindly try this and let know for any queries or clarification
Cheers @balkishan

1 Like

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 :slight_smile:

1 Like

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.

@balkishan, let me know if you need code for that :slight_smile:

1 Like

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…

1 Like

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 :slight_smile:

1 Like

sorrry bro not getting can you explain what I have to do.

1 Like

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 :slight_smile: @balkishan

1 Like

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

Cheers @balkishan

1 Like