Macro to find zero values in a certain column and delete tha

G

Guest

Guest
Does anyone have a VBA code that will look for a value of zero in say column P and delete the row containing this zero value?

I have three hundred spreadsheets of journal entries, but some of the journal lines have zero values so I can't upload those lines into our accounting software. This code would save me a tremendous amount of work.

Thanks to any takers!

Corey
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
The following macro will delete all rows containing any text you specify in column C. Hopefully it is obvious how to change it for other columns.

Sub DeleteZeroRows()

' This macro deletes all rows on the active worksheet
' that contain a zero value in column C.

Dim iRow As Long

For iRow = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If Cells(iRow, 3) = 0 Then Rows(iRow).Delete
Next iRow

End Sub
 
Upvote 0
On 2002-03-02 13:20, Damon Ostrander wrote:
The following macro will delete all rows containing any text you specify in column C. Hopefully it is obvious how to change it for other columns.

Sub DeleteZeroRows()

' This macro deletes all rows on the active worksheet
' that contain a zero value in column C.

Dim iRow As Long

For iRow = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If Cells(iRow, 3) = 0 Then Rows(iRow).Delete
Next iRow

End Sub


Damon's macro is OK provided the first row on the worksheet that contains data is row 1.
The following macro should work whatever the first row with data happens to be :-

Sub DeleteZeroRows()
Dim iRow As Long, firstRow As Long, lastRow As Long
firstRow = ActiveSheet.UsedRange.Cells(1, 1).Row
lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
For iRow = lastRow To firstRow Step -1
If Cells(iRow, 3) = 0 Then Rows(iRow).Delete
Next iRow
End Sub
 
Upvote 0
Hello, could you please help with a macro for deleting the zero and if the line below has the same account, partner and currency(monnaie) to be deleted to?
For instance, below 2 items must be deleted from the report (the report is very big, more than 7000 lines):

upderctsharecurncyaccntpartnermonnaieflowamount
2017.08S0709EURIP22100S8318NOKF990 to be deleted
2017.08S0709EURIP22100S8318NOKM992 to be deleted
2017.08S0709EURIA35900S0915GBPF993 to be kept
2017.08S0709EURIA35900S0915GBPM993 to be kept
2017.08S0709EURI715520S0001HKDF9923to be kept
2017.08S0709EURI715520S0001HKDM99215to be kept
2017.08S0709EURIH95333S0709EURF99150254to be kept
2017.08S0709EURIH95333S0709EURM99150254to be kept

<colgroup><col span="8"><col></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top