1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel - remove 0/blank rows data for csv conversion

Discussion in 'Business Applications' started by chudok01, Feb 1, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    201
    I have a spreadsheet that has is basically ready to be converted to a .csv file, It is basically a list of items that I want to convert to a csv file so I can import into our ERP system.

    The problem is I have some items with 0 qty on it and I do not want those rows going into our ERP system. I want to clean this up before I convert to a csv file.

    Sample
    Items # Qty
    1121 1
    1470 0
    1569 2

    I just want
    Items # Qty
    1121 1
    1569 2

    Is there a macro that I can run that will do this for me?
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    can't you just resort the list and manually delete the Zero Rows? unless you have to redo this many times, there is no point in a macro.

    If you are concerned about messing up the sort of the original list, then add a temporary number column starting at 1 and auto fill to bottom (before your sort), then sort on the column with ZERO's and after you delete resort on the column with the sequence and delete it.
     
  3. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    201
    This would have to be done many times that's why I am trying to find a way to automate it.
     
  4. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    use this....


    Code:
    Public Sub deleteRow1()
    
    
    
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim iLastRow As Long
        Dim x As Long
        
        
        Set wb = ThisWorkbook
        
        ' this line is disabled, but you can use it in place of the one that uses "ActivesSheet"
        ' it is useful for referring to a specific worksheet, which may not be active.....
        ' Set ws = wb.Sheets("Sheet1") 'set as necessary
        
            Set ws = ActiveSheet
        
        
        ' the number in the line below refers to the COLUMN to check for last row, adjust accordingly.
        iLastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
      
    
    
        For x = iLastRow To 1 Step -1
    
    ' currently set for column 1, change accordingly
            If Cells(x, 1) = 0 Then
                    
            Rows(x).EntireRow.Delete
                 
            End If
    
    
    
    
        Next x
    
    
    
    End Sub
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    To do this without looping:

    Sub test()
    Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=IF(RC[-1]=0,#N/A,1)"
    Columns(3).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
    Columns(3).Delete
    End Sub


    Assumes Items is col A, Qty is col B, col C is spare, sheet to be processed is active.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1039064

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice