In VBA code we can define a dynamic range by checking the last row that has data on it. this can be defined in various ways. Here is a simple way of doing it:
Dim rng As Range
rng = Range("A12:C" & Worksheets("Sheet1").Range("C65535").End(xlUp).Row + 1)
This will get us the range A12:C200 if C200 is the last used cell in column C.
You can also define a range that gets all of the columns in row 1 that are used as column headers, like this:
Dim ws As Worksheet
Dim rng As RangeSet
ws = ActiveSheetSet
rng = Intersect(ws.Rows(1), ws.UsedRange)
This will get us the range of A1:D4 if columns A, B, C and D have data on it.
