How do I delete rows of excel that have an exact value at once?

Question

If I have a chart on Excel Sheet contains 3000 rows and 5 columns, the last column has different values in each row, what if I need to delete the rows which have (0) on the last column - how can I do this without deleting each row separately one by one, is there a way I can tell Excel to delete each row that contains the value (0) at its end??

Answer 1

This works in Excel 2002 (XP) and onwards.

Highlight the data (or if the data is continuous just being "inside" it works)

From the menu select:

Data, Filter, AutoFilter

You will get a row of dropdown arrows across the top of the range.

In the end column click the dropdown arrow and select 0

This will show you all the rows with a value of zero.

Highlight all the zeros, right click the mouse and select "Delete Row"

From the menu select:

Data, Filter, AutoFilter  to remove the auto filter and show all your data (minus the zeros).

Answer 2

Assuming all five columns contain a value for every row, this macro will delete all rows that have a '0' in the 'last column'.

Change the column reference from 'A' in Line 3 from A1  and 'A65536' to your column reference before copying to the clipboard.

Open your workbook

Copy this macro to the clipboard

Sub DeleteZeroRows()

Dim rng As Range

Set rng = Range("A1:" & Range("A65536"). _

End(xlUp).Offset(1, 0).Address(0, 0))

Application.ScreenUpdating = False

For Each cell In rng

cell.Select

If ActiveCell.Value = 0 Then

ActiveCell.EntireRow.Delete

End If

Next

Application.ScreenUpdating = True

End Sub

Press ALT + F11

Insert  >  Module

Paste the macro into the Module space to the right

Close back to Excel

Go to Tools  >  Macro  >  Macros

Highlight the DeleteZeroRows macro, if it is not already highlighted.

Click:  Options

Select a Letter to be used as a keyboard shortcut.

Close back to Excel.

Press CTRL + your shortcut letter to call the macro.