[Solved] VBA : Line Deletion Macro Needed!

  • Hi everyone-I need some help on a catalog I am putting together in Excel. Here's the problem.


    I have a spreadsheet with products and their specifications. In the last column of this spreadsheet, there is either a part # or #N/A.


    What I would like to do is create a macro that will completely delete every line that has an #N/A in the last column.


    I cannot just sort the spreadsheet by the last column because it would change the order of the products.


    I'm sure there is a really simple answer to this that I am completely overlooking. Any ideas?

  • Use an autofilter on the last column. Set it to #N/A and delete the visible rows. By hand just select them. By macro use the SpecialCells to select the visible rows for your data not counting the first row, and then delete.

  • If your "#N/A" are in column E, and if column A as data contigous to the bottom of your data, then the following code will delete lines that have a function reporting an error in column E.


  • Assuming the first row is 2 and last column is ‘D’ try:


    Sub delete_rows()
    Const column = 4
    Dim rownum As Integer
    rownum = 2
    Do While Cells(rownum, column).Text <> ""
    If Cells(rownum, column).Text = "#N/A" Then
    Rows(rownum).Delete
    rownum = rownum - 1
    End If
    rownum = rownum + 1
    Loop
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!