How can I iterate from a specific Excel cell to the latest row having a value in this column using VBA?

excel vba excel-vba

50 观看


14743 作者的声誉

I am absolutly new in Excel and VBA macro and I have the following problem.

I wrote this code that print the content of the K column cell starting from the 3th row to the 5th row:

Dim outQuantityRange As Range
Set outQuantityRange = Range("K3:K5")

For Each currentOutQuantity In outQuantityRange.Cells
    MsgBox (currentOutQuantity)

It works fine. My problem is that I want change this code to access to the content of the cell into the K column starting from the 3th row and ending to the last inserted value. For example if the last value is into the K100 cell it have to print the content of: K3, K4, K5,......,K100.

I don't want to specify K100 but it have to stop to the last row having a value into the K column.

How can I implement this behavior? Next

作者: AndreaNobili 的来源 发布者: 2017 年 12 月 27 日

回应 (3)


14432 作者的声誉

I've given two ways to find the last cell - using the LastCell function will return the very last cell on the sheet which may not be in column K.

The second way I've shown is just finding the last cell in column K.

The range is then set by giving the first and last cell references separated by a comma.

Sub AllValues()

    Dim outQuantityRange As Range
    Dim currentOutQuantity As Range
    Dim rLastCell As Range

    With ThisWorkbook

        'Find last cell on sheet containing data.
        'Set rLastCell = LastCell(.Worksheets("MySheetName"))

        With .Worksheets("MySheetName")
            'Find last cell in column K containing data.
            Set rLastCell = .Cells(.Rows.Count, 11).End(xlUp)
            Set outQuantityRange = .Range("K3", rLastCell)
        End With
    End With

    For Each currentOutQuantity In outQuantityRange
        MsgBox currentOutQuantity, vbOKOnly + vbInformation
    Next currentOutQuantity

End Sub

Public Function LastCell(wrkSht As Worksheet) As Range

    Dim lLastCol As Long, lLastRow As Long

    On Error Resume Next

    With wrkSht
        lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
        lLastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row

        If lLastCol = 0 Then lLastCol = 1
        If lLastRow = 0 Then lLastRow = 1

        Set LastCell = wrkSht.Cells(lLastRow, lLastCol)
    End With
    On Error GoTo 0

End Function
作者: Darren Bartrup-Cook 发布者: 27.12.2017 05:27


78615 作者的声誉

If the values in column K are constants, then:

Sub qwerty()
    Dim outQuantityRange As Range, zell As Range

    Set outQuantityRange = Range("K3:K" & Rows.Count).SpecialCells(2)

    For Each zell In outQuantityRange.Cells
        MsgBox zell.Value
    Next zell
End Sub
作者: Gary's Student 发布者: 27.12.2017 05:29


4416 作者的声誉


If there are no gaps between K3 and last row, then this will do the work:

Dim rng As Range
Set rng = Range("K3", Range("K3").End(xlDown))
作者: JohnyL 发布者: 27.12.2017 05:56