Delete Row If....

[cpadilla0024]

Hi,

I am trying to clean up data lines that are not necessary for my tables. Ideally, if I could delete rows where columns B through G are all zeroes, that would be great. When I export my data it automatically includes these rows so a button would be perfect to be able to clean these with a snap.

I attached a small data set as an example but usually my data contains anywhere from 30,000-50,000 lines. It changes almost everyday. I am not sure if we should make this dynamic or if just realizing the columns B-G works. I do not want my column headers to be deleted in the process.

Please help! Thanks!

 

[AlanSidman]

Option Explicit

Sub DelZero()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = lr To 2 Step -1
        If WorksheetFunction.Sum(Range("B" & i & ":G" & i)) = 0 Then
            Range("B" & i).EntireRow.Delete
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "Action Complete"
End Sub

 

[Marc L]

Hi !​

​

The easy Excel way is to just filter the range - as there is no table in your attachment ! - then delete the filtered rows …​

But the more non contiguous rows, the slower to delete … (The same when deleting row by row within a For … Next loop.)​

​

So for many rows just follow a child way : in a helper column a formula returns TRUE for rows to delete and FALSE for those to keep​

then apply a sort on that column in order all the TRUE rows are at the end of the range then it's easy to clear them at once !​

Clear the helper column, that's all folks ! Faster even manually than creating any code, in particular for a punctual need …​

​

When a code is really necessary the easy procedure is to reproduce this way.​

So according to the attachment a beginner starter demonstration :​

Sub Demo1()
   Const C = 8
     Dim V
     Application.ScreenUpdating = False
With [A1].CurrentRegion.Resize(, C).Columns
    .Item(C).Value2 = Evaluate(.Item(7).Address & "=0")
    .Sort .Cells(C), xlAscending, Header:=xlYes
     V = Application.Match(True, .Item(C), 0)
     If IsNumeric(V) Then .Rows(V & ":" & .Rows.Count).Clear
    .Item(C).Clear
End With
     Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[cpadilla0024]

Hi! I tried the first code on my actual data set with several lines and it basically froze up my Excel.

The second one seems quicker but I do have data that stretches out to other columns that needs to be maintained for the rows that are False to the b through G all zeroes test.

If the only way will freeze up my Excel, that is fine. I was just hoping for a quick cleaner to make my data more compact and presentable. I could not include my whole file because it was too big. Sorry, I am kind of stuck just describing it.

 

[AlanSidman]

Do you have a lot of calculations in your file. If so, add a line of code to make the calculations manual and then at the end make them automatic. This is probably why you are freezing up. How to do It? See this https://www.automateexcel.com/vba/on-off-calculations/

 

[cpadilla0024]

There are a handful of lookups that are to the right of the data that I sent in. I could only send you a shortened version of the file because Chandoo was saying my original was too big. I will take a look at your article.

 

[p45cal]

This macro runs on the active sheet:

Sub blah()
Set myrng = Cells(1).CurrentRegion.Resize(, 7)
Range("K2").FormulaR1C1 = "=AND(RC[-9]=0,RC[-8]=0,RC[-7]=0,RC[-6]=0,RC[-5]=0,RC[-4]=0)"
myrng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("K1:K2"), Unique:=False
Set ddd = Intersect(myrng, myrng.Offset(1)).SpecialCells(xlCellTypeVisible)
ActiveSheet.ShowAllData
ddd.Delete shift:=xlShiftUp
Range("K2").Clear
End Sub

 

[Marc L]

The second one seems quicker but I do have data that stretches out to other columns
that needs to be maintained for the rows that are False to the b through G all zeroes test.

As it's one of the two fastest ways I know and as a beginner starter you can amend the formula if necessary …​

​