How to write If any cells in Column X = 0 Cut entire rows

[Tardisgx]

I have been relying on find and replacing specific values (in this case 0 with nothing) and then using special select entire (blanks.entirerow) which is janky code and doesn't work without an error wrap in case the special select finds nothing.

An If and else statement would be more elegant.

So Column X:X If cell = 0 select that entire row in order to cut it and paste in elsewhere.

 

[Fluff]

You could use autofilter for that.

 

[mumps]

You could filter column X on values equalling zero and then cut the visible range and paste elsewhere.

 

[Tardisgx]

I meant in vba; I don't know how to write the If statement; filters I would rather not use.

 

[Fluff]

Why don't you want to use an autofilter?
It will be far simpler & probably quicker

 

[Tardisgx]

The problem I have with autofilter is the defined ranges it uses; in this case $A$1:$AB$7; I think i have worked out a version in the past that does not write the ranges but I don't have it on hand right now.

Also the Rows 8:30 I selected after the filtering is specific and not dynamic and I don't know how to select the results of a filtered range if the range it produces will always be unique.

'
    Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AB$7").AutoFilter Field:=24, Criteria1:="=0", _
        Operator:=xlAnd
    Rows("8:30").Select
    Selection.Copy

 

[Fluff]

As long as you don't have any blank rows in the data, you can use

Sub Tardisgx()
   With ActiveSheet
      .Range("A1:AB1").AutoFilter 24, "0"
      With .AutoFilter.Range.Offset(1).EntireRow
         .Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
         .Delete
      End With
      .AutoFilterMode = False
   End With
End Sub

 

[Tardisgx]

That is so small I read it like it's skipping steps and doing 2 steps in 1 line. It's brilliant thanks.

 

[Fluff]

You're welcome & thanks for the feedback