Remove rows with zero values

[cecis]

Hello,
I have a spreadsheet where I plug in values from a chart of accounts and they are linked to another sheet.

	A	B	C	D
1	Account Number	Account Name	Account Type	Amount
2	10010	Cash	Asset	2049268.47
3	10030	Cash	Asset	0
4	11000	Accounts Receivable	Asset	15494.88
5	20000	Accounts Payable	Liability	-53661
6	22500	Credit Card Payable	Liability	0
7	30000	Capital Stock	Equity	-4455513.13
8	60000	Operating Expense	Expense	10000
9	62400	Consulting	Expense	0
10	63300	Recruiting and Relocation	Expense	12000

<tbody>
</tbody>

Column D is linked to another sheet that looks for the account with the specified account number. For different trial balances, some accounts will have zero values and others won't.
Is there a formula I can add in the adjacent columns that only reflects the rows with values in column D?

I would like something like:

	A	B	C	D
1	Account Number	Account Name	Account Type	Amount
2	10010	Cash	Asset	2049268.47
3	11000	Accounts Receivable	Asset	15494.88
4	20000	Accounts Payable	Liability	-53661
5	30000	Capital Stock	Equity	-4455513.13
6	60000	Operating Expense	Expense	10000
7	63300	Recruiting and Relocation	Expense	12000

<tbody>
</tbody>

Any help would be greatly appreciated.

 

[hiker95]

cecis,

If I understand you correctly.

Sample raw data:

Excel 2007
	A	B	C	D
1	Account Number	Account Name	Account Type	Amount
2	10010	Cash	Asset	2049268.47
3	10030	Cash	Asset	0
4	11000	Accounts Receivable	Asset	15494.88
5	20000	Accounts Payable	Liability	-53661
6	22500	Credit Card Payable	Liability	0
7	30000	Capital Stock	Equity	-4455513.13
8	60000	Operating Expense	Expense	10000
9	62400	Consulting	Expense	0
10	63300	Recruiting and Relocation	Expense	12000
11
Sheet1

Cell Formulas
Range	Formula
D2	=2049268.47
D3	=0
D4	=15494.88
D5	=-53661
D6	=0
D7	=-4455513.13
D8	=10000
D9	=0
D10	=12000

After the macro:

Excel 2007
	A	B	C	D
1	Account Number	Account Name	Account Type	Amount
2	10010	Cash	Asset	2049268.47
3	11000	Accounts Receivable	Asset	15494.88
4	20000	Accounts Payable	Liability	-53661
5	30000	Capital Stock	Equity	-4455513.13
6	60000	Operating Expense	Expense	10000
7	63300	Recruiting and Relocation	Expense	12000
8
9
10
11
Sheet1

Cell Formulas
Range	Formula
D2	=2049268.47
D3	=15494.88
D4	=-53661
D5	=-4455513.13
D6	=10000
D7	=12000

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub DeleteRowsColDZero()
' hiker95, 10/22/2014, ME813425
Dim r As Long, lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 2 Step -1
  If Cells(r, 4).Value = 0 Then Rows(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the DeleteRowsColDZero macro.

 

[apo]

Hi..

This will build a new array and replace your existing data.. you can change it so the new array gets displayed somewhere else (if you don't want your data replaced)..

Private Sub CommandButton1_Click()
    Dim x, y, i As Long, ii As Long, cnt As Long
    With Range("A1").CurrentRegion
        x = .Value: cnt = 1
        ReDim y(1 To Application.CountIfs(.Columns(4), "<>0") - 1, 1 To 4)
        For i = 1 To UBound(x) - 1
            If x(i + 1, 4) <> "0" Then
                For ii = 1 To 4
                    y(cnt, ii) = x(i + 1, ii)
                Next ii
                cnt = cnt + 1
            End If
        Next i
        .Offset(1).ClearContents
        .Offset(1).Resize(UBound(y)).Value = y
    End With
End Sub