Copy rows based on char length

[emukiss10]

Hello,

I need help with macro to copy rows to new sheet based on length.

if cell in column J has 9 or 14 characters than copy entire row to new sheet.

I have something like this for another column and that is working but now I need length for another..

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "XXX"


    Sheets(1).Select
    Rows("1:1").Select
    Selection.Copy
    Sheets("XXX").Select
    Rows("1:1").Select
    ActiveSheet.Paste
    Sheets(1).Select


Dim cell1 As Range
Dim lastRow1 As Long, i As Long


lastRow1 = Range("H" & Rows.Count).End(xlUp).row
i = 2


For Each cell1 In Sheets(1).Range("H1:H" & lastRow1)
    If cell1.Value = "3" Or cell1.Value = "4" Then
        cell1.EntireRow.Copy Sheets("XXX").Cells(i, 1)
        i = i + 1
    End If
Next
Worksheets("XXX").Columns("A:BB").AutoFit
    Range("A1").Select

Help

 

[Scott T]

Something like this should work. change sheet names and ranges as need.

lr = Sheets(1).Cells(Rows.Count, "J").End(xlUp).Row

Dim cell As Range
i = 2
For Each cell In Sheets(1).Range("J2:J" & lr)
    If Len(cell) >= 9 And Len(cell) <= 14 Then
        cell.EntireRow.Copy Sheets("XXX").Range("A" & i)
        i = i + 1
    End If
Next cell

 

[emukiss10]

Thank You!

 

[Scott T]

You're welcome.

 

[armywalrus2]

I have a bunch of data that is not uniform - would this work to copy all cells with less than 30 characters to another sheet? I wouldn't want the whole row, just the cell containing the less than 30 characters.