What does Range("Q1048576") mean

Question

I am new to VBA and I have to work with a previous colleague's work. I have a code to auto-refresh an excel file, and I am trying to understand this range ("Q1048576") as I have several such in other scripts.. Thanks for your assistance

code

Sub RefreshSalesData()

Dim Location9 As String
Dim Archive9 As String
Dim Location9Archive9 As String

Sheets("Data").Select
Range("Q1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Now()


Location9 = "C:\Users\fred\Documents\data_to_refresh"
Archive9 = "SalesData.xlsx"
  
Location9Archive9 = Location9 & Archive9

Answer 1

1048576 is a hard-coded last row number in Q column. It is rather bad practice, because it is not compatible with older .xls format (.xlsx has exactly 1048576 rows, .xls about 65k) and would cause run-time error, because 1048576 row doesn't exists in .xls files. But even worse is using select for no purpose How to avoid select.

Change this fragment:

Sheets("Data").Select
Range("Q1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Now()

To:

With ThisWorkbook.Sheets("Data")
   .Cells(.Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = Now()
End With

Also remeber to fully qualify your ranges. For example:

Sheets("Data")

is implicite the same as:

ActiveWorkbook.Sheets("Data")

which can be dangerous probably if you have few workbooks open. If you want to make changes in current workbook (which contains the macro code), you can use:

ThisWorkbook.Sheets("Data")