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

Help Activities
, ,
1

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

image
image880×219 4.85 KB

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

2

Hi @StevenIsRobotOnline

You can ytry with filter Data Table activity

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

image
image801×475 30.4 KB

Regards
Gokul

3

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

image
image874×1031 36.6 KB

Hope it helps!!

4

Hi,

Can you try the following sample?

image
image1117×811 39.8 KB

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,

5

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

thank you

6

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

7

Hi @StevenIsRobotOnline

Check with my solution.

Regards

8

Hi Pravi ,

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

9

Hi,

How about the following classic version?

image
image1122×1010 29.1 KB

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

Regards,

1 Like
10

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

11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.