Creating loop that stops after certain string

I import our entire trial balance into Excel but for the purposes on this particular spreadsheet, I am only interested in the accounts prior to and including the "total income" account. Therefore, I would like to create an array from a loop that stops after reaching "total income". I would then like to make a drop-down of that list (which may vary in length based on the client and their particular trial balance) but not include any extra blanks. Does anyone know how to do this?

Thank you!!!

Please post a sample workbook with some typical data and expected results.

Hi letsExcelebrate

Welcome to the Forum!!!

This Code does not create an array...uses AutoFilter to accomplish what you've requested...CTRL + k will fire the Code.

Option Explicit

Sub Test()
   Dim SrcWs        As Worksheet
   Dim TgtWs        As Worksheet
   Dim SrcLR        As Long
   Dim TgtLR        As Long

   Set SrcWs = Sheets("Before")
   Set TgtWs = Sheets("After")

   Application.ScreenUpdating = False
   With TgtWs
      .Cells.Clear
   End With

   With SrcWs
      SrcLR = .Columns("A").Find(what:="Total Income", LookIn:=xlValues, lookat:=xlWhole).Row
      .Range("A3:B" & SrcLR).AutoFilter Field:=2, Criteria1:="<>"
      .Range("A5:A" & SrcLR).SpecialCells(xlCellTypeVisible).Copy
      TgtWs.Range("A1").PasteSpecial
      .AutoFilterMode = False
      Application.CutCopyMode = False
   End With

   With TgtWs
      TgtLR = .Range("A" & .Rows.Count).End(xlUp).Row
      ActiveWorkbook.Names.Add Name:="My_Accounts", RefersTo:=Range("A1:A" & TgtLR)
      With .Range("C1").Validation
         .Delete
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:="=My_Accounts"
      End With
      .Range("C1").AutoFill Destination:=.Range("C1:C" & TgtLR), Type:=xlFillDefault
      .Range("D1").Formula = "=IFERROR(VLOOKUP(C1,Before!A:B,2,FALSE),"""")"
      .Range("D1").AutoFill Destination:=.Range("D1:D" & TgtLR), Type:=xlFillDefault
   End With
   Application.ScreenUpdating = True
End Sub

Hi letsExcelebrate

You're welcome...glad I could help. Thanks for the Rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.