macro to delete rows that do not contain a 6 digit number

[howard]

I have mainly 6 digit numbers in Col B on sheet2


I need code that will delete all rows from row 2 onwards where there is not a 6 digit number in Col B


See sample data below. I want all the rows to be deleted from row 2 where there is no 6 digit number

Book1
	B
2	933808
3	933750
4	933752
5	933910
6	935050
7	Short Des
8	Fact
9	Disc
10	COS
11	Not in use
12	933815
13	933816
14	933845
15	Sales Item1
16	933872
17	933801
18
Sheet1

 

[nemmi69]

something like this?

Sub DelRow()


LastRowNo = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row


For RowLoop = LastRowNo To 2 Step -1
    If Worksheets("Sheet1").Range("B" & RowLoop).Value < 100000 Or IsNumeric(Worksheets("Sheet1").Range("B" & RowLoop).Value) = False Then
        Rows(RowLoop & ":" & RowLoop).Delete
    End If
Next RowLoop
End Sub

 

[Fluff]

How about

Sub delRows()
   With Range("B2", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if((isnumber(@))*(len(@)=6),@,"""")", "@", .Address))
      .SpecialCells(xlBlanks).EntireRow.Delete
   End With
End Sub

 

[howard]

Thanks for the help guys. Code works perfectly

 

[Fluff]

Glad we could help & thanks for the feedback