Ah the generic, where is my data question:
Sub dataRange() With Sheets("Names").Columns("C") 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 End Sub |