MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Macro to Suppress Rows w/ Zero Value

  Asked By: Maria    Date: Sep 03    Category: MS Office    Views: 1901

I have written a macro to reformat a downloaded data file to be
imported into a third party application software(converting file to
*.csv format). I would like to include in the macro the code to
suppress all rows that have a cell value of zero.



4 Answers Found

Answer #1    Answered By: Earl Stone     Answered On: Sep 03

Very few people are programming gurus. Most of us just muddle
through and hope we don't affect some critical operation. Can you
define "suppress" for us? Do you want to show a blank cell, delete
the cell, hide the row, etc.?

If you just want the cell  to be blank, a number format  might
be best.

Answer #2    Answered By: Anna Hill     Answered On: Sep 03

I would like the entire row to be deleted. Thanks for your assistance.

Answer #3    Answered By: Alexander Fields     Answered On: Sep 03

This assumes that you are on the correct worksheet. That column 1
contains the zero values. That the data  starts in row 1.

Sub DeleteRows()
'' Checks cells in first column for 0. Deletes these rows.

Dim lngRow As Long, lngL As Long

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

lngRow = Cells(65536, 1).End(xlUp).Row

For lngL = lngRow To 1 Step -1
If Cells(lngL, 1).Value=0 Then Cells(lngL, 1).EntireRow.Delete
Next lngL

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub

Answer #4    Answered By: Vivian Ruiz     Answered On: Sep 03

You do say, "cell value of zero", and Paul furnished you code  for that. But just
in case the value is not 0, but is "" or blank, this will work fast and
efficiently without looping. It will not, however, remove a row when Column A
cell has a value of 0 entered into it. It will remove blank, "", cell  rows. Just
thought I would give you another option in case the cell value is actually ""
instead of 0.

On Error Resume Next 'In case there are no blank cells
On Error GoTo 0

Didn't find what you were looking for? Find more on Macro to Suppress Rows w/ Zero Value Or get search suggestion and latest updates.