score:3

Accepted answer

if you delete rows in a loop, you need to start from the bottom. otherwise the row number of the rows below the actual deleted row decrease by one and i gets increased by one (from the loop), so in sum you miss a line.

instead of

for i = 1 to 40

use

for i = 40 to 1 step -1
    if worksheets("sheet4").cells(1, i) = 0 then
        columns(i).entirecolumn.delete
    end if
next

to loop backwards.


side note: (thx to @a.s.h)
you should use full qualified ranges/cells/rows/columns and never assume the worksheet. also declare all your variables using option explicit.

therefore

rows(1).insert shift:=xlshiftdown
'...
columns(i).entirecolumn.delete

should be

worksheets("sheet4").rows(1).insert shift:=xlshiftdown
'...
worksheets("sheet4").columns(i).entirecolumn.delete

so in sum we end up at

option explicit 'first line in module

public sub reorder()
    dim i as long
    dim wy as worksheet

    set wy = worksheets("sheet4")

    wy.rows(1).insert shift:=xlshiftdown

    for i = 1 to 40
        select case wy.cells(2, i)
            case "physical location", "plc tag name", "test step1", "test step2", _
                 "test step3", "test step4", "test step5", "test step6", "test step7"
                wy.cells(1, i) = 1
            case else
                wy.cells(1, i) = 0
        end select
    next i

    for i = 40 to 1 step -1
        if wy.cells(1, i) = 0 then
            wy.columns(i).entirecolumn.delete
        end if
    next
end sub

or if we use only one loop which is a lot faster:

option explicit 'first line in module

public sub reorder()
    dim i as long
    dim wy as worksheet

    set wy = worksheets("sheet4")

    wy.rows(1).insert shift:=xlshiftdown

    for i = 40 to 1 step -1
        select case wy.cells(2, i)
            case "physical location", "plc tag name", "test step1", "test step2", _
                 "test step3", "test step4", "test step5", "test step6", "test step7"
                wy.cells(1, i) = 1
            case else
                wy.columns(i).entirecolumn.delete
        end select
    next i
end sub

Related Query

More Query from same tag