Delete the Zero values

[Abhijeet]

Hi

I have data in excel & i want Delete the Cell value if ZERO & Entire Range(B2:K17) if Zero Then Delete Entire Row.I have show in attach file expected result.please tell me how to do this

 

[Abhijeet]

I have this Macro to delete the given range any bank cell then delete the entire Row

Public Sub Delete_Row_or_Range()
          Const DELETE_ENTIRE_ROW = True
          Dim Is_It_Blank As Boolean
          Dim Number_of_Columns As Integer
          Dim Number_of_Rows As Long, First_Row As Long, Last_Row As Long, i As Long
          Dim Range_To_Be_Checked As Range, rw As Range
          Set Range_To_Be_Checked = Range("B2:K100")
          With Range_To_Be_Checked
                First_Row = .Row
                Number_of_Rows = .Rows.Count
                Number_of_Columns = .Columns.Count
          End With
          Last_Row = Number_of_Rows - First_Row + 1
         
          Application.EnableEvents = False
          Application.ScreenUpdating = False
         
          For i = Last_Row To 1 Step -1
              Set rw = Range_To_Be_Checked.Rows(i).Cells
              Is_It_Blank = Application.WorksheetFunction.CountBlank(rw) = Number_of_Columns
              If Is_It_Blank Then
                  If DELETE_ENTIRE_ROW Then
                    rw.EntireRow.Delete
                  Else
                    rw.Cells(1, 1).Value = 1
                  End If
              End If
          Next
         
          Application.EnableEvents = True
          Application.ScreenUpdating = True
End Sub

 

[Smallman]

Hi Abhijeet

Give the followining a try.

Sub GetRid()
Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("B2:L" & lr).Replace "0", ""
    Range("L2:L" & lr).Formula = "=SUM(B2:K2)"
    Range("L1:L" & lr).AutoFilter 1, 0
    Range("L2:L" & lr).EntireRow.Delete
    Range("L2:L" & lr).Clearcontents
    [L1].AutoFilter
End Sub

Take care

Smallman

 

[Abhijeet]

Thanks for this I tried with changes in my macro
Public Sub Delete_Row_or_Range()
Const DELETE_ENTIRE_ROW = True
Dim Is_It_Blank As Boolean
Dim Number_of_Columns As Integer
Dim Number_of_Rows As Long, First_Row As Long, Last_Row As Long, i As Long
Dim Range_To_Be_Checked As Range, rw As Range
Set Range_To_Be_Checked = Range("B2:K20")
With Range_To_Be_Checked
First_Row = .Row
Number_of_Rows = .Rows.Count
Number_of_Columns = .Columns.Count
End With
Last_Row = Number_of_Rows - First_Row + 1

Application.EnableEvents = False
Application.ScreenUpdating = False

For i = Last_Row To 1 Step -1
Set rw = Range_To_Be_Checked.Rows(i).Cells
Is_It_Blank = Application.WorksheetFunction.CountIf(rw, 0) = Number_of_Columns
If Is_It_Blank Then
If DELETE_ENTIRE_ROW Then
rw.EntireRow.Delete
Else
rw.Cells(1, 1).Value = 1
End If
End If
Next

Range_To_Be_Checked.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub