Find range which contains first cell only of last line?

get value of last non-empty cell
excel find first cell with value in row
excel find last row with specific value
excel find last cell with value in column
excel find first cell with value greater than 0
excel address of last cell with data
excel return column number of last non blank cell
excel formula to find last non zero value in a column

I need to calculate a range which contains just a single cell. This cell is from the last line and first column. If the sheet is empty the range is A1:A1.

I know there are plenty of ways to calculate the last line, but I'm looking for an elegant way to get the first cell of the last line. Maybe some examples explain better.

Example #1

 A B C D
1
2  X
3      X
4    X

Result #1

Range = A4:A4

Example #2

 A B C D
1

Result #2

Range = A1:A1

How to do this?

If I have understood correctly, you want find the last row across some range (or bunch of columns).

One way to achieve this might be to loop over each column within the range, find what row the last cell (in that particular column), and check if it exceeds whatever the greatest last row has been thus far in the loop.

In the code below, if you change "Sheet1" to whatever your sheet is called, and change the range from "A4:Z5" to something like "A:Z" or "A1:D4" (or whatever it is in your case), it should then display the address of the cell you're after.

Option Explicit

Private Sub ShowLastCell()
    ' Change this to what your sheet is called.
    With ThisWorkbook.Worksheets("Sheet1")

        ' Change this to the range you need to check.
        With .Range("A4:Z5")
            Dim firstColumnToCheck As Long
            firstColumnToCheck = .Columns(1).Column

            Dim lastColumnToCheck As Long
            lastColumnToCheck = .Columns(.Columns.Count).Column
        End With

        Dim maxLastRow As Long
        Dim columnIndex As Long

        For columnIndex = firstColumnToCheck To lastColumnToCheck
            maxLastRow = Application.Max(maxLastRow, .Cells(.Rows.Count, columnIndex).End(xlUp).Row)
        Next columnIndex

        MsgBox ("I think the cell you want is " & .Cells(maxLastRow, "A").Address & ":" & .Cells(maxLastRow, "A").Address)

    End With
End Sub

Excel formula: Address of last cell in range, To get the address of the first cell in a named range, you can use the adding ROWS(data)-1, so that the first item in the array is the last row number: When entered in a single cell, just the item from the array is displayed, which In the example, the active cell contains this formula: = COLUMNS ( B4:C9 ) Here's how the. Find the Last Number in a Range. If your range contains only numbers, you can use this formula to find the last cell containing a number in the range: =INDEX(C$2:C$13,MATCH(1E+100,C$2:C$13,1)) Note: for a change this isn’t an array formula. The trick with this formula is the 1E+100. 1E+100 is just a really big number.

GetFirstCellInLastLine will return the first cell in the last line of the referenced worksheet as a Range object. Then you can do what you want with it. For example, printing to Immediate Window for the active sheet:

Debug.Print GetFirstCellInLastLine(ActiveSheet).Address

It is setup to return Noting if the worksheet is blank, but you can modify this according to your needs:

'''
''' Returns the first used cell in the last line of the worksheet.
''' Returns "Nothing" if the worksheet is blank.
'''
Public Function GetFirstCellInLastLine(ws As Excel.Worksheet) As Excel.Range

Dim rng As Excel.Range

    Set rng = ws.UsedRange.Cells(ws.UsedRange.Rows.Count, 1)

    If ((ws.UsedRange.Columns.Count > 1) And ws.Range(rng, rng.End(xlToRight)).Columns.Count <= ws.UsedRange.Columns.Count) Then
        Set rng = ws.Range(rng, rng.End(xlToRight))
        If VBA.IsEmpty(rng.Cells(1, 1)) Then
            Set rng = rng.Cells(1, rng.Columns.Count)
        Else
            Set rng = rng.Cells(1, 1)
        End If
    ElseIf (ws.UsedRange.Columns.Count = 1) And VBA.IsEmpty(rng.Cells(1, 1)) Then
        Set rng = Nothing
    End If

    Set GetFirstCellInLastLine = rng

End Function

Return the First and Last Values in a Range • My Online Training Hub, The INDEX function returns a reference to a cell and when used on its own, of values returned by the ROW function, now just tell me the MAX value. If your range contains only numbers, you can use this formula to find the  where "things" is the named range E5:E9.. Note: this is an array formula and must be entered with Control + Shift + Enter.. How this formula works. In this example, we have a list of of colors in a named range called "things" (B5:B11).

Last Used Row & Specified Column Intersection feat. UsedRange

One of the elegant ways would be to use the UsedRange property.

Advanced Version
'*******************************************************************************
' Purpose:    Using the UsedRange Property, creates a reference to the cell    *
'             range at the intersection of the last used row and a specified   *
'             column in a worksheet and prints its address and the address     *
'             of the UsedRange to the Immediate Window.                        *
'*******************************************************************************
Sub LastUR_Column_UsedRange()

  Const cVntCol As Variant = "A"  ' Column
  Dim objRngT As Range            ' Target Range

  With ThisWorkbook.Worksheets("Sheet1")
    If .Cells(.UsedRange.Rows.Count + .UsedRange.Row - 1, cVntCol).Row = 1 _
        And .Cells(1, Columns.Count).End(xlToLeft).Column = 1 _
        And IsEmpty(.Cells(1, 1)) Then
      Debug.Print "objRngT = Nothing (Empty Worksheet)"
     Else
      Set objRngT = .Cells(.UsedRange.Rows.Count + .UsedRange.Row - 1, cVntCol)
      Debug.Print "objRngT = " & objRngT.Address & " calculated from the " _
          & "used range (" & .UsedRange.Address & ")."
      Set objRngT = Nothing
    End If
  End With

End Sub
'*******************************************************************************
Lesson Version
'*******************************************************************************
' Purpose:    Using the UsedRange Property, creates a reference to the cell    *
'             range at the intersection of the last used row and a specified   *
'             column in a worksheet and prints subresults and  its address     *
'             to the Immediate Window.                                         *
'*******************************************************************************
Sub LastUR_Column_UsedRange_Lesson()

  ' When you declare the column as variant you can use
  ' column letter or number e.g. "A" or 1, "D" or 4 ...
  Const cVntCol As Variant = "A"    ' Column

  Dim objRngT As Range              ' Target Range

  Dim lngLast As Long               ' Last Row
  Dim lngRows As Long               ' Number of Rows
  Dim lngFirst As Long              ' First Row

  With ThisWorkbook.Worksheets("Sheet1")

    ' Finding first row and number of rows is easy.
    lngFirst = .UsedRange.Row
        Debug.Print "lngFirst  = " & lngFirst
    lngRows = .UsedRange.Rows.Count
        Debug.Print "lngRows   = " & lngRows

    ' Note1: Only when there is data in the first row, the number of rows
    '        is equal to the last row.

    ' Therefore we have to calculate the last row.
    lngLast = lngRows + lngFirst - 1
        Debug.Print "lngLast   = " & lngLast

    ' Now imagine you have the first data in row 2, and you have 3 rows
    ' which would mean the last data is in row 4 (rows 2, 3, 4). So when you add
    ' 2 + 3 = 5, you have to subtract 1 row, because you counted row 2 twice.

    ' Note2: If there is data in the first row then lngFirst = 1.
    '        So the formula will calculate:
    '          lnglast = lngRows + 1 - 1
    '          lngLast = lngRows + 0
    '         which proves the statement in Note1.

    ' The previous three lines could have been written in one line:
    lngLast = .UsedRange.Rows.Count + .UsedRange.Row - 1
        Debug.Print "lngLast   = " & lngLast & " (One Row Version)"

    ' Now we have all the ingredients for the Target Range.
    Set objRngT = .Cells(lngLast, cVntCol)
        Debug.Print "objRngT   = " & objRngT.Address _
            & " (Before Check if Empty)"

    ' And again all this could have been written in one line:
    Set objRngT = .Cells(.UsedRange.Rows.Count + .UsedRange.Row - 1, cVntCol)
        Debug.Print "objRngT   = " & objRngT.Address & " (One Row Version)" _
             & " (Before Check if Empty)"
    ' then you wouldn't need variables lngLast, lngFirst and lngRows. On the
    ' other hand you wouldn't have learned how this big formula was created.

    ' Now the problem is that if the worksheet is empty, UsedRange will show
    ' the cell in the first row as the used range. So we have to address this
    ' issue by checking if all of the following three conditions are true.
    ' - Check if the resulting cell range is in the first row (1).
    ' - Check if from the end of the first row to the beginning the result
    '   is the first cell (1) (all other cells are empty).
    ' - Check if the cell ("A1") is empty.
    If objRngT.Row = 1 And _
        .Cells(1, Columns.Count).End(xlToLeft).Column = 1 And _
        IsEmpty(.Cells(1, 1)) Then
      Debug.Print "objRngT   = Nothing (Empty Worksheet)"
     Else
      Debug.Print "objRngT   = " & objRngT.Address
    End If
    ' Although this is a working code, we can conclude that we should have done
    ' this checking at the beginning which will be done in the advanced version.

  End With

  Set objRngT = Nothing

End Sub
'*******************************************************************************

How to extract the first or last line from a multi-line cell in Excel?, Extract and copy the last line text from multi-line cells with formula all first line text contents have been extracted at once, see screenshot: Select the range of cells that you want to use, and then click Kutools > Text > Split Cells, see screenshot: And then, click OK button, and each line text has been extracted to separate  How to check the UsedRange. The easiest way to check the currently UsedRange in an Excel Worksheet is to select a cell (best A1) and hitting the following key combination: CTRL + SHIFT + END. The highlighted Range starts at the cell you selected and ends with the last cell in the current UsedRange.

Last Used Row & Specified Column Intersection feat.
The Find Method

I would call this the safest and most elegant way: using the Find method.

'*******************************************************************************
' Purpose:    Using the Find method, creates a reference to the cell range at  *
'             the intersection of the last used row and a specified column     *
'             in a worksheet and prints its address to the Immediate window.   *
'*******************************************************************************
Sub LastUR_Column_Find()

  Const cVntCol As Variant = "A"  ' Column Letter or Number ("A" or 1)
  Dim objRngT As Range            ' Target Range

  With ThisWorkbook.Worksheets("Sheet1")
    If Not .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), -4123, , 1) _
        Is Nothing Then
      Set objRngT = .Cells(.Cells.Find("*", , , , , 2).Row, cVntCol)
      Debug.Print "objRngT = " & objRngT.Address
      Set objRngT = Nothing
     Else
      Debug.Print "objRngT = Nothing (Empty Worksheet)"
    End If
  End With

End Sub
'*******************************************************************************
' Remarks:    If you carefully study the "Find method as it applies to         *
'             the Range object." from "Visual Basic Help", you will realize    *
'             why exactly those four arguments and their parameters in         *
'             the If statement must be included and why three of them can      *
'             be omitted, but a new one has to be added in the Else clause.    *
'*******************************************************************************

How to return the first / last non blank cell in a row or column?, This formula also can be applied to get the first non blank cell value in a row, you just need to 2. This formula just work correctly on single row or single column. It works to find last non blank cell that has more than 255 characters, also. The Range.Find Method Explained. The Find method is looking for the first non-blank cell (“*”). The asterisk represents a wildcard character that looks for any text or numbers in the cell. Starting in cell A1, it moves backwards (xlPrevious) and actually starts it's search in the very last cell in the worksheet.

Excel Annoyances: How to Fix the Most Annoying Things about Your , 10 100 % the active row , while Ctrl - left arrow selects the first cell used in the active row . Ctrl - End takes you to the bottom right corner of the worksheet ; Ctrl - Home When I scroll down , the headers that tell me which columns contain which the top row headings visible on her worksheets If you try , you ' ll see only the  Then press Enter key, and you will get the first non blank cell value as follows: Notes: 1. In the above formula, A1:A13 is the column range that you want to use, you can change it to your need. This formula also can be applied to get the first non blank cell value in a row, you just need to change the column range to row range. 2.

Numerical Techniques in Finance, The range name DATA is attached to the first cell of the return vector of security 1. As in chapter 8, we let the macro record the time it takes to calculate the betas The third line of \ A is invoked only after the loop is finished. When we finish the first line of NB, the cell at the bottom of PRODUCT contains the fi of security 1. You can also use the CELL function to get the address of the last cell in a range with this formula: = CELL("address",INDEX( data,ROWS( data ),COLUMNS( data ))) In this case, we use INDEX to get a reference to the last cell in the range, which we determine by passing total rows and total columns into INDEX. The reference returned by INDEX is then handled by the CELL function, using "address", to return the address.

Learning to Love Microsoft Excel Visual Basic for Applications , Row This fifth line of code instructs the application to start the While The loop will end in the first empty cell, within column A that it comes to. a double “nn” text string is found, i.e., if cell A2 contains “Kenny,” and cell A3 contains “penny,” This is done only after the loops statements have finished looping; that means that  Note: For the new line delimiter on Windows, you'll want to use CHAR(10). On Excel for Mac, use CHAR(13). The CHAR function returns a character based on it's numeric code. The number of spaces used to replace the line delimiter is based on the total length the text in the cell. The formula then uses the MID function to extract the desired line.

Comments
  • This code will fail if the last used row is hidden, and will fail if the last used column is hidden AND the last used cell is contained in it. But this might be an interesting functionality whose purpose I would define as follows: Shows the address of the cell at the intersection of the last unhidden used row and column "A" of columns of a specified range in a message box.