Macro Select All Rows until Formula is FALSE

[tatertot]

Hello Excel world,

I want to perform the LEN formula in a macro for column D. I want to test "=LEN(D:D)=9" and where the last TRUE is highlight all rows above it, including the last TRUE. I have scoured the internet with absolutely zero success in knowing if this is possible or not.

Can this be done? I am wanting to copy the rows, if it is possible, and paste to a new worksheet.

 

[Peter_SSs]

Give this a try in a copy of your workbook.

Sub CopyOnLength()
  Dim a As Variant
  Dim i As Long
  
  a = Range("D1", Range("D" & Rows.Count).End(xlUp).Offset(1)).Value
  Do Until Len(a(i + 1, 1)) <> 9
    i = i + 1
  Loop
  If i > 0 Then
    Application.ScreenUpdating = False
    ActiveSheet.Copy After:=ActiveSheet
    Rows(i + 1).Resize(UBound(a) - i).Delete
    Application.ScreenUpdating = True
  Else
    MsgBox "No rows to copy"
  End If
End Sub

 

[tatertot]

Peter, thank you so much for your reply to this thread with code!! I inserted the code into my macro but was unlucky in having it find what it should and be able to copy that data.

 

[Peter_SSs]

.. was unlucky in having it find what it should and be able to copy that data.

I don't understand this. Can you expand on it and/or provide some small sample data to demonstrate?