delete a row in an excel file

[KJ]

I am attempting to use this line of code from a very old post:

oWorkbook.ActiveSheet.Rows(2).Delete;

This doesn't seem to work.  The Test Complete type ahead only contains Cell, CelCount and Row Count.  I simply want to delete one row or a range of rows from an excel file. 

Any assistance is appreciated.

[Marsha_R]

What is it that you are trying to do with the data that ends up with you deleting it?  Best practice is to not delete your test data.  Perhaps we can find you another way to run the test without doing that.

[KJ]

Actually the task is a little bit trickier then that.  I have three excel files.  I need to manipulate each one, for example

Excel1 - delete the first five rows and the last row of data, then copy all data in three columns and paste into Excel3.

Excel 2 - copy data from three columns and then paste into Excel3

This kind of task was doable in UFT, but now that I am using Test Complete, it's a lot different.

Any help is appreciated.

[Marsha_R]

How do you get this data into Excel1 and Excel2 in the first place?

[KJ]

Data from Excel 1 and Excel 2 come from outside data sources.  Excel 3 contains formulas which will compute the information from Excel 1 and Excel 2.  The data itself is not actually important, I just need to delete specific rows from the spreadsheet.  

[AlexKaras]

Hi,

> This doesn't seem to work

If https://stackoverflow.com/questions/31878154/delete-rows-x-to-y-in-on-a-specific-excel-worksheet does not help, could you elaborate what does 'This doesn't seem to work' mean?

[KJ]

Alex,

I found that I need to access the excel com directly to at least delete a row.  using this

wkbExported = appExcel.Workbooks.Open("\\\\network\\\\folder1\\\\folder2\\\\folder3\\\\datafile_20210113.csv");

wkbExported.ActiveSheet.Range("1:1").Delete();

or to delete columns using this:

wkbExported.ActiveSheet.Range("J:J").Delete();

Is there a paste option when copying from one file to another?  I need to paste a large amount of data from one spreadsheet to another.  Please advise.

[AlexKaras · Accepted solution]

Hi,

> Is there a paste option

For example:

https://www.google.com/search?q=excel+vbscript+copy+paste+data

https://stackoverflow.com/questions/28816210/excel-vbscript-copy-and-paste-cell-values-to-end-of-ano...

And the whole Excel COM documentation.

[sonya_m]

Thanks to everyone for participating in this discussion!

@KJ Does the advice help?

[edwards142]

I have some simple fixes using which you can easily delete blank rows in Excel.

1. Choose the data set > press Ctrl + F keys to open the Find and Replace dialog.

2. Hit the “Options” button > choose “Values” from “Look in” And let the “Find What” text box blank > click “Find All”.

3. Dialog box of “Find and Replace” will highlight entire blank cells.

4. Click any one record > press Ctrl + A for selecting the entire blank cells.

5. Close the dialog box of “Find and Replace” and without clicking anywhere else go to Home > Delete > Delete Rows.

6. Doing this will delete the entire selected rows.

For more tricks like this, check out this link.

source: www.repairmsexcel.com/blog/delete-blank-rows-in-excel