Deleting Rows by Specific Criteria (Number of Digits)

[cherub85]

I'm working with a large data set and attempting to build a Macro to clean up and format the data. This data set is updated and released yearly, my thought here is to create a Macro that I can dump the file in every year and have it cleaned up and formatted so as to save time.

Column B has an amount of digits that varies from 1-6, For example:

Column B
1
52
243
5434
13456
785324

I simply want to delete all of the rows that have a total of 5-6 digits in Column B and keep the rows with 1-4 total digits in Column B.
Notes: I'm referring to the number of digits not the number values.
As in 13456 = 5 digits = Delete and 243 = 3 digits = Keep
Each record runs from columns A:O
Total number of Rows is 976272

I am learning as i go during this process, so I would appreciate any help you can provide.

 

[JoeMo]

Are the numbers in column B numbers or text?
Do you want to delete records only or the entire row?
Is there a header row? If not, in what row do the numbers begin?

 

[cherub85]

Numbers only in Column B, no text.

I want to delete the entire row when the Macro identifies a 5 or 6 digit match.
So the Macro would find for example 532456 (6 digit) in B10 as it runs through, the Macro would then say so long to Row 10 and bump up the remaining Rows to fill the deleted Row. This process would continue until all 5 & 6 matches/rows have been deleted.

 

[JoeMo]

Numbers formatted as text may look like numbers but are actually text. If you pick an empty cell somewhere on the sheet and enter =TYPE(B10), does the formula return a 1 or a 2?

What row do the "numbers" start in?

 

[cherub85]

It is returning type 1, they appear to be all numbers without text.

the numbers start in row 2 after my header row.

 

[JoeMo]

Try this on a copy of your worksheet. This will not delete 5-digit numbers with a leading 0 or 6-digit numbers with a leading 00.:

Sub Remove5And6DigitRows()
Dim sh As Worksheet, lRw As Long, delRws As Range, ct As Long

Set sh = ActiveSheet
lRw = sh.Range("B" & Rows.Count).End(xlUp).Row

For Each c In sh.Range("B2", "B" & lRw)
    If Trim(c.Value) >= 10000 Then
        ct = ct + 1
        If delRws Is Nothing Then
            Set delRws = c
        Else
            Set delRws = Union(c, delRws)
        End If
    End If
Next c
If delRws Is Nothing Then
    MsgBox "No 5 or 6 digit numbers in column B"
Else
    delRws.EntireRow.Delete
    MsgBox ct & " rows with 5 or 6 digit numbers were deleted"
End If
End Sub

 

[Akinrotimi]

The code below would resolve your request.Do not put any data on column C so that it does not distort your existing data:

 Application.ScreenUpdating = False
    Range("C10").Select
    ActiveCell.FormulaR1C1 = "=IF(OR(RC[-1]="""",LEN(RC[-1])>4),"""",""ok"")"
    Range("C10").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(OR(RC[-1]="""",LEN(RC[-1])>4),"""",""ok""),"""")"
    Range("C10").Select
    Selection.AutoFill Destination:=Range("C1:C10"), Type:=xlFillDefault
    Range("C1:C10").Select
    Selection.AutoFill Destination:=Range("C1:C1000000")
    Range("C1:C1000000").Select
    Range("C1").Select
    Columns("C:C").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C1").Select
    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
    Range("C1").Select
    Columns("c:c").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "good"
    Columns("C:C").Select
    Selection.ClearContents
    Range("C1").Select
    Application.ScreenUpdating = True
 
end sub

Regards

Rotimi