CODEWITHSUNDEEP
excelvba
Concerned_Citizen
Concerned_Citizen

Reputation: 6845

Loop Through First Nonempty Cell to Last Nonempty Cell

I am trying to loop through the first occurrence of a cell up to some unknown last nonempty cell. For example.

I know how to find the last and first nonempty cell but how can I put them in a loop?

 With Worksheets("AssignedTickets").Columns("F")
    Set test = .Find(what:="*", after:=.Cells(1, 1), LookIn:=xlValues)
End With

enter image description here

Upvotes: 1

Views: 155

Answers (1)

user6432984
user6432984

Reputation:

Here is a couple of techniques:

enter image description here

Sub LoopThroughCells()
    Dim c As Range, Target As Range, rFirst As Range, rLast As Range
    Dim x As Long, y As Long

    With Worksheets("Sheet1").Cells
        Set rLast = .Find(What:="*", _
                          After:=.Cells(1), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False)

        Set rFirst = .Find(What:="*", _
                           After:=rLast, _
                           Lookat:=xlPart, _
                           LookIn:=xlFormulas, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=False)


        With .Range(rFirst, rLast)
            Debug.Print "For Each c In .Cells"
            For Each c In .Cells
                If x <> c.Row Then Debug.Print
                x = c.Row
                Debug.Print c.Value,
            Next
Stop
            Debug.Print
            Debug.Print "For x = 1 To .Rows.Count"
            For x = 1 To .Rows.Count
                Debug.Print

                For y = 1 To .Columns.Count
                    Debug.Print .Cells(x, y),
                Next
            Next

        End With
    End With


End Sub

Note: A For Each Loop to iterates over a range row by row (e.g. All cells in Rows(1) then all the cells in Rows(2) ..etc.).

UPDATE:

Selecting the range starting from the first used cell and last used cell; without using find.

 With Worksheets("Sheet1")
    With .Range(.Range("C1").End(xlDown), .Range("C" & Rows.Count).End(xlUp))
        For Each c In .Cells
            If x <> c.Row Then Debug.Print
            x = c.Row
            Debug.Print c.Value,
        Next
    End With
End With

Upvotes: 2

Related Questions