How do I delete a row based on the published within the J column = 0?

Question

I have the following in Excel spreadsheet,

a cut down version of my spreadsheet

It would be better to cut the row and paste it within another sheet.. but IF you can help me with just deleting it, that would be good.

If you only need this once, it might be easiest to use an auto-filter and copy/delete the rows by hand. It's hard to tell if you're really looking for an automated solution. — Tom Juergens, Oct 21 '11 at 10:38
Yeah thats what i have been doing. I am build INSERT statements in Excel for the various products etc... — PD24, Oct 21 '11 at 10:45
@pd24 as you have tagged vba I have answered you the vba solution which is quite faster way to do — niko, Oct 21 '11 at 10:59
If your interested in optimizing the code see: http://stackoverflow.com/questions/7261933/optimize-excel-vba-code/7263655#7263655 — Jon49, Oct 21 '11 at 14:37

niko · Answer 1 · 2011-10-21T11:02:40.677

Sub Delete_Zero_Codes()  ' Deletes The Zero  Codes 
    Dim rCell As Range
    Dim strAddress As String
Application.ScreenUpdating = False

    With Thisworkbook.Sheets("sheetname").Columns("J")
        Set rCell = .Find(What:=0, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns)
        If Not rCell Is Nothing Then
            Do
            strAddress = rCell.Address
            rCell.EntireRow.Delete
            Set rCell = .FindNext(Range(strAddress))
            Loop Until rCell Is Nothing
        End If
    End With

Application.ScreenUpdating = True

End Sub

These macro deletes zero codes of J column and does not paste them to other sheet, if you need zero codes of J column to be copied to other sheet then let me know i will update it

+1 Niko. I added my version as it also did the copy component. Your Find routine will be very quick if there aren't too many "0" values to be found — brettdj, Oct 21 '11 at 12:05

score 3 · Accepted Answer · answered Oct 21 '11 at 11:39

This code autofilters the rows on the activesheet where J =0, copies them to the first blank row on the second worksheet, then deletes the rows from the activesheet.

Change this line Set ws2 = Sheets(2) to copy the rows to a different sheet, ie Set ws2 = Sheets("Your Sheet Name") or Set ws2 = Sheets(5) for the fifth sheet etc

Sub MoveEm()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws1 = ActiveSheet
    Set ws2 = Sheets(2)
    Dim rng1 As Range
    Application.ScreenUpdating = False
    With ws1
        .AutoFilterMode = False
        .Columns("j").AutoFilter Field:=1, Criteria1:="0"
        With .AutoFilter.Range.Offset(1, 0).EntireRow
        Set rng1 = ws2.Cells.Find("*", ws2.[a1], xlValues, , xlRows, xlPrevious)
        If rng1 Is Nothing Then
        Set rng1 = ws2.[a1]
        Else
        Set rng1 = ws2.Cells(rng1.Row + 1, "A")
        End If
            .Copy rng1
            .Delete
        End With
        .AutoFilterMode = False
    End With
    Application.ScreenUpdating = True
End Sub

+1 for complete answer but brett I belive find is fast too, I have deleted 1000 rows out of 5000 within 2 seconds , — niko, Oct 21 '11 at 12:09
Find is indeed very quick niko, but its efficiency is diminished when it is needed to be called many times (either as another find or findnext) for many succesful matches, particularly when there is processing after each found result (in this case three codes inside the Do loop). I tend to keep Find for searching for scarce results, AutoFilter with a working column is my favoured approach — brettdj, Oct 21 '11 at 12:15
Yeah I agree auto filter is more fast because it is optimised to its best ! even I use auto filter , I just wanted to post a different soltuion :) — niko, Oct 21 '11 at 12:18

How do I delete a row based on the published within the J column = 0?

2 Answers2