How to delete only the rows which have 0 values on some columns as condition

[StevenIsRobotOnline (Steven)]

Hi Guys ,

I have an excel file with formula in each cell , and i want avoid re writing all the data inside the excel because if we use write range on all of data range, then the formula will also be deleted.

so i only want to delete the rows which have condition where there are 3 particuluar column with value of 0

Condition :
Note : on the process Im using excel application scope
column1= 0
column2=0
column3=0
Example

Please let me know if you have any suggestion ya,
Thank you

[Gokul001 (Gokul Balaji)]

Hi @StevenIsRobotOnline

You can ytry with filter Data Table activity

It will remove all the row if it contain 0 column1, column2,column3

Regards
Gokul

[pravallikapaluri]

Hi @StevenIsRobotOnline

Change the sheetname in the code and excel path
BlankProcess4.zip (618.4 KB)

sub deleteRowsWithZeros()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to the name of your sheet
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For i = lastRow To 2 Step -1
        If ws.Cells(i, 1).Value = 0 And ws.Cells(i, 2).Value = 0 And ws.Cells(i, 3).Value = 0 Then
            ws.Rows(i).Delete
        End If
    Next i
End sub

Hope it helps!!

[Yoichi]

Hi,

Can you try the following sample?

arrIdx = dt.AsEnumerable.Where(Function(r) r("Column1").ToString="0" AndAlso r("Column2").ToString="0" AndAlso r("Column3").ToString="0").Select(Function(r) dt.Rows.IndexOf(r)+3 ).OrderByDescending(Function(i) i).ToArray

Sample
Sample20231011-4L.zip (14.9 KB)

Regards,

[StevenIsRobotOnline (Steven)]

Hi Yoichi , im using classic version of excel application scope , is there a way to dit it on calssic version?

thank you

[StevenIsRobotOnline (Steven)]

if we are implementing this , then we need to use write range activity and all formula will once again be re write .
i would like to avoid that

thanks

[pravallikapaluri]

Hi @StevenIsRobotOnline

Check with my solution.

Regards

[StevenIsRobotOnline (Steven)]

Hi Pravi ,

thanks for the suggestion but i dont think this approch is suitable on the process im enhancing

[Yoichi]

Hi,

@StevenIsRobotOnline

is there a way to dit it on calssic version?

How about the following classic version?

Sample20231011-4L (2).zip (14.7 KB)

Regards,

[StevenIsRobotOnline (Steven)]

Thanks for the idea everyone ,

but i found a solution where
i get the start index using linq
then get the last index using row count

then i can just use delete range for the unwanted rows

cheers