CODEWITHSUNDEEP
excelvba
skrtoff
skrtoff

Reputation: 11

How to delete rows where cell value = 0?

I want to speed up some of my current tasks.
The steps I saw best fit

  1. duplicate far left (most current) tab
  2. copy range I9 to last cell in column with data (including zeros, data above total line)
  3. paste values of copied ranged in D9
  4. delete/clear cells E9:H24 (if this is done before the copy and paste values of I9, the values in range I9 will be incorrect)
  5. delete sheet rows for cells in range D9:last cell in column D with values = 0
  6. insert 4 rows below last cell in range A9:lastcellwithdata
  7. Go to prior sheet, copy first 4 columns for range A:E with values in column E (almost always last 4-5 rows of data)
  8. return to current sheet, paste values (copied in 7) under last cell in column A with data (paste where you inserted the rows, maybe steps 6-8 can be done by copying and inserting instead of inserting blank rows first) (This has to be done after step 4 as cells will be pasted over the cells deleted in step 4)
  9. for range just pasted in current tab, column C value for entire range changed to current value +1
  10. prior sheet, copy range J:M for cells with values in column E
  11. current sheet, paste normal copied cells from step 10 to Column J under last cell with data in data range (above total row)
Public Sub CopySheetAndRename()
    Application.OnKey "^+k", "CopySheetAndRename"
    Dim newName As String
 
    On Error Resume Next
    newName = InputBox("Enter the name for the copied worksheet")
 
    If newName <> "" Then
        ActiveSheet.Copy Before:=Sheets(1)
        On Error Resume Next
        ActiveSheet.Name = newName
    End If
   
    Dim lr As Long
    lr = Cells(Rows.Count, 12).End(3).Row

    Range("I9:I" & lr).Copy
    
    ActiveSheet.Range("D9").PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Range("E9:H24").Clear

    Rows("25:29").Insert Shift:=xlDown, _
      CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

I have gotten thru steps 1-4 and step 6.

enter image description here

Assume P2 is the most current tab, and P3 is the tab that will be created. I took that screenshot after running my macro, I should have deleted that tab before uploading here.

Per the example, all workbooks (150 plus) have the same headers in rows 1-7, a blank row at 8, and the data starting in row 9. All workbooks have data starting from A9:M. The number of rows is inconsistent.

I want to start pulling data from row 9 down to the last column with data before the first blank row (above the totals line).

How do I delete sheet rows from D9:D for all cells with values = 0? This can't delete rows with blank cells, only cells = 0.

Upvotes: 0

Views: 1141

Answers (1)

NoobVB
NoobVB

Reputation: 1033

example of the simple loop via column D to delete 0 values (not empty ones). the main row is

If ws.Cells(c, 4).Value = 0 And ws.Cells(c, 4).Value <> "" Then

Note, deleting rows via Loop is always better from the bottom to the top:

Option Explicit
Sub Macro4()

Dim wb As Workbook: Set wb = ThisWorkbook 
Dim ws As Worksheet: Set ws = wb.Sheets("P3")
Dim c As Long

For c = ws.Cells(ws.Rows.Count, "D").End(xlUp).row -5 To 2 Step -1 ' note -5 is to offset last row number due to Total/Diff/Balance rows. Or simply use another "clean column" to get the last row number
    If ws.Cells(c, 4).Value = 0 And ws.Cells(c, 4).Value <> "" Then
    Rows(c).EntireRow.Delete
    End If
Next c

End Sub

Upvotes: 0

Related Questions