There are pros and cons with just about any method you choose to reference the dynamic block of data you wish to include with your report.
The caveat that comes with .SpecialCells(xlLastCell) is that it may encompass a cell that was previously used but is no longer within the scope of the data. This could occur if your data shrinks from one month to the next although recent service packs and updates provided for Excel 2010/2013 will shrink the rogue last cell through saving the workbook. I'm sure many of us have at one time or another mistyped a value into AZ1048576 and had to jump through hoops getting Excel to internally resize the extents of the actual data. As mentioned, with later versions of Excel, this problem is all but a footnote in history.
One last thing to note is that cells formatted as anything but plain-Jane General will halt the shrink so if last month's report had a formatted Subtotal line 50 rows below where it is this month, the .SpecialCells(xlLastCell) will be referencing an area 50 rows too long.
If you have a contiguous block of data with some blank cells possible but no fully blank rows or columns that segregate your data into islands then I prefer the following approach.
With sheets("Sheet1")
Set rng = .Cells(1, 1).CurrentRegion
End With
The area referenced by the above code can be demonstrated by selecting A1 and tapping Ctrl+A once (twice is A1:XFD1048576). The cells selected will be a rectangle encompassing the last column with data and the last row with data as the extents. Previously used cells and cells that have retained formatting from previous reports have no effect; only cell values and formulas. However, it must be emphasized that the island of data stops at the first fully blank row or column.
The last method I will mention is to position in (aka .Select or otherwise start at) A1 and use .Find with a wildcard searching backwards so that it ends up starting at XFD1048576 and searching toward A1 for the first value or formula it can find first by row then repeated by column.
Dim lr As Long, lc As Long, rng As Range
With Sheets("Sheet3")
lr = .Cells.Find(What:=Chr(42), After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lc = .Cells.Find(What:=Chr(42), After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set rng = .Cells(1, 1).Resize(lr, lc)
End With
This is really the only true method of getting an accurate representation of your data block but its thoroughness is not usually necessary.
