Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

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

  1. #1
    Guest

    Default

    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

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Guest

    Default

    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

  4. #4
    New Member
    Join Date
    Oct 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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):

    upder ctshare curncy accnt partner monnaie flow amount
    2017.08 S0709 EUR IP22100 S8318 NOK F99 0 to be deleted
    2017.08 S0709 EUR IP22100 S8318 NOK M99 2 to be deleted
    2017.08 S0709 EUR IA35900 S0915 GBP F99 3 to be kept
    2017.08 S0709 EUR IA35900 S0915 GBP M99 3 to be kept
    2017.08 S0709 EUR I715520 S0001 HKD F99 23 to be kept
    2017.08 S0709 EUR I715520 S0001 HKD M99 215 to be kept
    2017.08 S0709 EUR IH95333 S0709 EUR F99 150254 to be kept
    2017.08 S0709 EUR IH95333 S0709 EUR M99 150254 to be kept

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •