vba repeating code untill column a will be empty

Question

How can i create code which will repeating code until columna a value will be empty

this is my code.

Sheets("sheet4").Select
Sheets("sheet4").Range("$A$1:$AG$2336").AutoFilter Field:=1, Criteria1:= _
   Sheets("sheet1").Range("a3")
Sheets("sheet4").Range("a1:ad1").find(Sheets("sheet1").Range("L3").Value).offset(2, 0).Select
       Do Until ActiveCell.EntireRow.Hidden = False
         ActiveCell.offset(2, 0).Select
       Loop
Selection.Copy Sheets("sheet1").Range("b3")
Sheets("sheet1").Select
End Sub

I need to copy my selection down until column a will end ( i mean cell in column a will be empty). Can u please help me ??

[Find the last cell](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Mar 19 '21 at 17:40
Yea but my data could change any time. And also i want to know how can i copy down. Take the value from Sheets("sheet1").Range("a3") filter in sheets("sheet4") copy this value Sheets("sheet4").Range("a1:ad1").find(Sheets("sheet1").Range("L3").Value).offset(2, 0) after then paste in sheet1 range b3 and go down take next value in sheet(sheet1).range (a4) and do the same. — Salim, Mar 19 '21 at 17:49
Could you post some screenshots to explain what you're trying to do? Maybe share a practical example like if Sheet!A3 = "Test" then find "Test" in ... Best do it in your [post](https://stackoverflow.com/posts/66713041/edit). — VBasic2008, Mar 19 '21 at 18:14

score 0 · Accepted Answer · answered Mar 20 '21 at 10:09

Replace the 3 with a variable and put the code in a loop.

Option Explicit

Sub macro()
    Dim wb As Workbook
    Dim ws1 As Worksheet, ws4 As Worksheet
    Dim colA, colL, iRow As Long

    Set wb = ThisWorkbook
    Set ws1 = wb.Sheets("Sheet1")
    Set ws4 = wb.Sheets("Sheet4")
    ws4.Select

    iRow = 3
    colA = ws1.Cells(iRow, "A")
    Do While Len(colA) > 0
        
        colL = ws1.Cells(iRow, "L")
        If Len(colL) > 0 Then
            ' apply filter
            ws4.Range("A1:AG2336").AutoFilter Field:=1, Criteria1:=colA
            ' copy filtered data
            ws4.Range("A1:AD1").Find(colL).Offset(2, 0).Select
            Do Until ActiveCell.EntireRow.Hidden = False
                ActiveCell.Offset(2, 0).Select
            Loop
            Selection.Copy ws1.Range("B" & iRow)
            '
        End If
        ' next value in col A
        iRow = iRow + 1
        colA = ws1.Cells(iRow, "A")

    Loop
    MsgBox iRow - 3 & " rows scanned on " & ws1.Name, vbInformation
End Sub

Thank you very, i am new in vba and i dont understand how loop is working and thats why i couldnt do this. — Salim, Mar 22 '21 at 10:51

vba repeating code untill column a will be empty

1 Answers1