VBA snippet – find data range

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"
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


Leave a Reply

Your email address will not be published. Required fields are marked *

4 + five =