|First, you should be aware that your use of UsedRange.Rows.Count will not always return the last row with data.|
Open a new workbook and run this code:
Range("B3") = "End of Data"
Range("A1") = "Start of Data"
Range("C10").Font.Bold = True
With data is B3 only, UsedRange.Rows.Count will return 1. Therefore, using LastDataRow = UsedRange.Rows.Count will not accurately reflect the last row with data, which is actually 3.
With data in A1 and B3, LastDataRow will return 3 which is correct since Row 1 contains data and therefore all rows are counted. Unfortunately we can't always be sure that Row 1 will contain data.
Finally, since the UsedRange includes cells that have been formatted, formatting C10 will increase the UsedRange.Rows.Count well beyond the last row with data.
The more common way to determine the last row with data is as follows, assuming you know that Column A is the longest (or equal to the longest) column with data:
LastDataRow = Range("A" & Rows.Count).End(xlUp).Row
If you don't know which Column is the longest, then you can loop through the used columns and determine which one is the longest.
For colNum = 1 To ActiveSheet.UsedRange.Columns.Count
If Cells(Rows.Count, colNum).End(xlUp).Row > LastDataRow Then
LastDataRow = Cells(Rows.Count, colNum).End(xlUp).Row
As far as the syntax you are looking for, I believe that you should be using something like this:
Range("A1:A" & LastDataRow)
Since a hardcoded range would look like Range("A1:A10"), you have to give VBA everything it wants when you build that range with a variable.
"A1:A" concatenated with a variable gives VBA the full range address, assuming that the variable contains a valid row number, e.g. 10.
"A1:A" & LastDataRow = "A1:A" & 10 = "A1:A10"
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.