How can I iterate from a specific Excel cell to the latest row having a value in this column using VBA?
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 日
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.
作者: Darren Bartrup-Cook 发布者: 27.12.2017 05:27
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
If the values in column K are constants, then:
作者: Gary's Student 发布者: 27.12.2017 05:29
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
If there are no gaps between K3 and last row, then this will do the work:
作者: JohnyL 发布者: 27.12.2017 05:56
Dim rng As Range Set rng = Range("K3", Range("K3").End(xlDown))