Need To delete bulk excel data

[sivaranjanising]

Hi,
I am handling excel which is having around 70k records daily,
1) I have 3 sheets namely Brand,Sheet1,Position, values are copied from Brand sheet and pasted into sheet1
2)once value pasted into sheet1 of respective columns the E and F of sheet1 will be auto populated based on the formulas
3)I have to filter E column where value =N/A which is around 30k
4)after filtering I have to delete these values so that 3rd sheet which is position gets updated automatically.

The problem is when I do it manually it takes 25 mins to delete entire filtered data rows, Bot takes an hour
I tried using OLEDB but it says deletion cannot be performed by linked table ISAM.
Sheet1:Screenshot below
Note: Column A:D having data it's blank


Please suggest me a good way to do this.
Thanks.

[EVIPUTI]

Using Delete command you can’t delete data from Excel as a database because OLEDB provider only allows to select, insert or update records to excel sheet. It does NOT allow delete operations. You can use the update command to delete your data , simply set the columns to NULL where value = #N/A


​

[sivaranjanising]

@vipul Tiwari,
It's not working below is the query
"UPDATE [Sheet1$]
SET [ISO COUNTRY CODE]=null,
SET[BRAND CODE]=null,
SET[BRAND POSITION CODE]=null,
SET[MARKET CODE]=null
WHERE [category]='N/A'"

It's showing below error,

Please advise.​

[sivaranjanising]

Hi,
Is there any code to filter data and delete those filtered data faster, Please help me on this.

Thanks,
Sivaranjani.

[krishna.kodimurali9]

Hi Sivaranjani,

There is no inbuilt action to delete filtered rows in excel. We need to create a custom action for that which gonna take time and we only should invest that time when its that necessary.

for the time being, you can use the excel macro to filter and delete the resultant visible rows .
You can update the code according to your requirments.No need to mention the sheet range, just update the last column, as it gets the range till the last row .
Field- the Numeric equivalent of the column
Creteria1- Filter criteria to be selected

Sub DeleteFilteredRows()
'
' DeleteFilteredRows Macro
'
Range("A1").Select
Selection.AutoFilter
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Range("$A:$X").AutoFilter Field:=3, Criteria1:="#N/A"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
Selection.AutoFilter
Range("A2").Select
End Sub