How to delete Top 10 rows from Excel

[MohiniShelke]

^{Hello All,} 
I want to delete Top 10 rows from Excel and get it into new excel. 

I don't want these in collection because we have around 20000 data in Excel, And collection will take more time to write data into Excel. 

Please let me know the solution for this if anyone knows. 


Thanks in advance

------------------------------
Mohini Shelke
RPA Developer
vElement It
------------------------------

[RajeshKumar · Accepted solution]

​Hi,

Try to run this macro using Excel VBO Run macro

Sub DeleteTop10()
    Rows("1:10").Select
    Selection.Delete Shift:=xlUp
End Sub


Change range as per your requirement

------------------------------
Rajesh kumar Muthineni
RPA Developer
Highmark Health
America/Chicago
------------------------------

[MohiniShelke]

Hey,

Thanks,Its worked for me.
I created separte macro file to run this but one problem my file name is changing.

------------------------------
Mohini Shelke
RPA Developer
vElement It
------------------------------

[CarlCarter]

This can be done very easily in Blue Prism without the need to introduce an Excel Macro, which has the potential of introducing an unnecessary failure point.

Use the Excel VBO Action 'Select' followed by the 'Delete' Action.

The 'Cell Reference' Input Parameter for the Select Action would be "1:10".
The 'Shift' Input Parameter for the Delete Action would be "Up".

------------------------------
Carl Carter
Developer Program Manager
Blue Prism
Europe/London
------------------------------

[MohiniShelke]

Thank you so much.

Worked for me.. and easy solution

------------------------------
Mohini Shelke
RPA Developer
vElement It
------------------------------

[AdarshKappettu]

Hi,

I tried Select- Delete VBO to delete rows in a Excel file. However I get "HRESULT .xxxxx " error. I want to clear all data in the sheet and write new data from the Collection. Kindly suggest how to proceed.

Thanks
Adarsh

------------------------------
Adarsh Kappettu
------------------------------

[JennaSchnizlein]

Is it always the same rows of the file?  I just ran into this for one of our reports.  I don't use the VBO, I just use Excel's shortcut keys.

Click in Cell A1,
Highlight the cells of the rows you want to delete "<{SHIFT}" "{DOWN}", etc.   ">{SHIFT}"
Then "^{SUBTRACT}"  (Ctrl -)
Then "r"  (to indicate you want to delete the entire row
Finally "{ENTER}" to close that box.

If there is a better way, using the VBO's I would be interested in learning it as well.  Hope this helps!

------------------------------
Jenna Schnizlein
------------------------------

[CarlCarter]

Hi,

There are several ways to achieve this depending on your requirements.

If the Worksheet is to be cleared of all data, then you could call the Delete Worksheet Action and then recreate it using the Create Worksheet Action.

You could also use the Select Action to select a column range; either to select the known columns or a large set of columns, by providing the start and end column reference as the Cell Reference input in the Action. For example "A:ZZ". This would select all rows and columns in the range A1 - ZZ:used range. Then you call the Delete action.

Another option is to extend the Excel VBO by creating a new action to select the Used Range. Easiest way to do this is to copy the existing Select Action into a new Action and name it Select Used Range. Then change the line of code in the new Action Code stage from

range = sheet.Range(Reference)

to

range = sheet.UsedRange


Call this new action followed by the existing Delete Action.

------------------------------
Carl Carter
Developer Program Manager
Blue Prism
Europe/London
------------------------------

[ZdeněkKabátek]

Hi, Adarsh,

just to be sure - did you open the excel with Excel VBO? Because before you can use Select-Delete actions you have to open excel file with Excel VBO.

Regards,

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------

[AdarshKappettu]

​Hi Zdeněk Kabátek,

Yes, I used the Excel VBO as below:
1.Create Instance
2.Open Workbook
3.Activate sheet
4.Select Data range
5. Delete

At stage 5 Delete is get the "HRESULT...." error.

Thanks
Adarsh


------------------------------
Adarsh Kappettu
------------------------------