Ron de Bruin wrote:
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

Thanks, I basically used that idea. But I wanted to keep the rest of
the code intact, so this is the section that sets the range from A1 to
the last used cell.

lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row
lastCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Column
Set Rng = ActiveSheet.Range(Cells(1, 1), Cells(lastRow, lastCol))