CODEWITHSUNDEEP
vbaexcel
m4sterbunny
m4sterbunny

Reputation: 97

VBA identify first and last rows with data

I want first and last rows with non-blank cells. My last row works fine, the first row is bust. Suggestions appreciated.

Sub idDataRange()

Dim firstRow As Long
Dim lastRow As Long

Sheets("fileNames").Select

' this has been adapted from a Stack overflow answer. lastRow unedited
' first row I changed Up to Down = NOT the solution!
With ActiveSheet
    firstRow = .Range("B" & .Rows.Count).End(xlDown).row
    lastRow = .Range("B" & .Rows.Count).End(xlUp).row
    End With

    MsgBox "the first row is " & firstRow
    MsgBox "last row is " & lastRow

End Sub

Upvotes: 2

Views: 39204

Answers (4)

dudshev
dudshev

Reputation: 1

'USING features of SELECTION

firstrow = Selection.Value

lastrow = Cells(Selection.Rows.Count + ActiveCell.Row - 1, ActiveCell.Column)

Upvotes: -1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

Using Find:

Edit: Doesn't appear to find the first cell if it's A1.
I've added .Cells(.Rows.Count, .Columns.Count) to the two Find rows. It will still muck up if the last cell on the sheet is populated - but in 19 years I've never filled an entire sheet with data.

Sub test()

    Dim rLastCell As Range

    MsgBox LastCell(Sheet1).Address 'Last Cell
    MsgBox LastCell(Sheet1, 1).Address 'First Cell.

End Sub

'---------------------------------------------------------------------------------------
' Arguments : Direction = 2 :Find Last Cell, 1 :Find First Cell
'---------------------------------------------------------------------------------------
Public Function LastCell(wrkSht As Worksheet, Optional Direction As Long = 2) As Range

    Dim lLastCol As Long, lLastRow As Long

    On Error Resume Next

    With wrkSht
        lLastCol = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), , , xlByColumns, Direction).Column
        lLastRow = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), , , xlByRows, Direction).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

Upvotes: 2

Michael
Michael

Reputation: 4848

This line works by starting at the bottom of the B column and then working up:

lastRow = .Range("B" & .Rows.Count).End(xlUp).row

To find the first row, you need to start at the top of the sheet and then work down instead, but also checking that the first row doesn't have anything:

firstRow = iif(isempty(.Range("B1")),.Range("B1").End(xlDown).row,1)

Note that the formula for lastRow assumes that there is no data in the very last cells of column B. Also, my formula for firstRow assumes there is at least one cell in column B with a value.

Upvotes: 2

user3598756
user3598756

Reputation: 29421

if values in column B are not derived from formulas, then you can use SpecialCells():

Dim firstRow As Long
Dim lastRow As Long

With Sheets("fileNames").Columns("B") '<--| reference your sheet (activating it is bad practice!) column "B" range
    If WorksheetFunction.CountA(.Cells) = 0 Then '<--| if no data whatever
        MsgBox "Sorry: no data"
    Else
        With .SpecialCells(xlCellTypeConstants) '<--| reference its cells with constant (i.e, not derived from formulas) values)
            firstRow = .Areas(1).Row
            lastRow = .Areas(.Areas.Count).Cells(.Areas(.Areas.Count).Rows.Count).Row
        End With
        MsgBox "the first row is " & firstRow
        MsgBox "last row is " & lastRow
    End If
End With

Upvotes: 5

Related Questions