remove redundant fields

[somenoob]

hi everyone, how do i remove redundant fields such as..

Account
60
35
0
0
10

in the example, i want to remove the 0s when i click a button.
how do i code it?

Thanks

 

[Rick Rothstein]

Deleting the 0's how? Clear the cell so only an empty cell remains in their place? Delete the cell (moving only the cells under it up)? Delete the entire row the 0's are on?

Also, you are talking about removing all the items that have a duplicate, or only the duplicates (leaving the first 0 on the worksheet)? And are we talking about all duplicates or only the values (such as 0) that you specify somehow (if yes, how)?

 

[somenoob]

thanks for the reply. i want to delete the whole row with the 0.

so long as under the Account column, if the number is 0, i want to delete the whole row.

 

[sprimax]

Just take a look at this thread

http://www.mrexcel.com/forum/showthread.php?t=576703&highlight=deleting+row

 

[somenoob]

thanks for the link.

the codes provided:

Sub DeleteRowsWithZeros()
Dim LstRw As Long, Rw As Long
Application.ScreenUpdating = False
LstRw = Cells(Rows.Count, "E").End(xlUp).Row
For Rw = LstRw To 1 Step -1
If Cells(Rw, "E") = 0 Then Rows(Rw).EntireRow.Delete
Next Rw
Application.ScreenUpdating = True
End Sub
----------------

however, HalfAce also said that "Note, this will delete the rows that contain a zero (formula or constant) and the rows that have a blank cell in column E as well.
"

i do not want to delete blank cell. so how do i edit the codes?

Also when i try to run the macro, it does not remove the row with 0.

 

[Rick Rothstein]

Assuming your Account's column is Column E (you can change this in the Const statement) and assuming your values in the Account's column are constant values (that is, they are not formulas), then you can use this code to delete the 0-rows...

Sub DeleteZeroRows()
  Dim Zeroes As Range
  Const AccountColumn As String = "E"
  With Columns(AccountColumn)
    .Replace "0", "=0", xlWhole
    On Error Resume Next
    .SpecialCells(xlFormulas).EntireRow.Delete
  End With
End Sub

 

[somenoob]

thanks for the reply. Do i need to specify which spreadsheet is it?

what the 0 is derived from a formula?

 

[Rick Rothstein]

thanks for the reply. Do i need to specify which spreadsheet is it?

what the 0 is derived from a formula?

As written, the code works directly on the active worksheet, but you can always qualify any range reference with a worksheet reference if you would like to be able to run the macro no matter what worksheet is active. For example, assuming the worksheet name is 'Client Accounts'...

Sub DeleteZeroRows()
Dim Zeroes As Range
Const AccountColumn As String = "E"
Const AccountSheet As String = "Client Accounts"
With Worksheets("Client Accounts").Columns(AccountColumn)
.Replace "0", "=0", xlWhole
.SpecialCells(xlFormulas).EntireRow.Delete
End With
End Sub

As for your other question, it can be done if your column is composed of all formulas instead of all constants, but it will need more code to handle that situation. Is that what you have? And, if so, are your blanks actually formulas displaying the empty string ("")?

 

[somenoob]

erm no. my 0s are just numbers. i just wanted to ask what if they are formulas. But Thanks alot for your help. it really helped me. Can you help me with another problem?
http://www.mrexcel.com/forum/showthread.php?t=579735
Please help me