Reference Data End in Macro

Jenawade

Board Regular
Joined
Apr 8, 2002
Messages
231
I recorded a macro based on downloaded data that will be of varying length whenever downloaded. The columns are always A-P, but the last line changes. When I recorded the macro and used the autofill, my macro recorded the exact range that existed in the report I used at that time.

To cover various length reports, I changed those autofills in the macro to go down to row 5000, and my pivot table to cover down to row 5000, which worked fine until I had to run a year-to-date report yesterday based on weekly data instead of the usual monthly data. When I finally realized that my table was missing data beyond line 5000, I changed my macro again, now covering all the way down to 61545, just in case.

This means I end up with spare bits of useless data at the end of my real data: a comma in column L, from concatenating, and 1, 1, 1900 in F, G & H from breaking out month, quarter & year.

Is there a simple way that I can change my row 61545 references in the macro to instead reference the final line of my data, whatever that may be in any given report?

Thank you,
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
You can obtain the number of rows used in a worksheet in a number of ways, here is one which stores the number of rows to the variable NoOfRows:
Code:
NoOfRows = Sheets("Sheet1").UsedRange.Rows.Count
 

Jenawade

Board Regular
Joined
Apr 8, 2002
Messages
231
Excellent. How do I then reference that variable? For instance, how do I reference it in this line:

Selection.AutoFill Destination:=Range("F2:H61545")




Thank you!
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Code:
Selection.AutoFill Destination:=Range("F2:H" & NoOfRows)

You are basically contructing a string which comprises of some text and some variables to refer to the range. text and variables are concatenated with an ampersand (&).

Hope that makes sense!! :)
 

Jenawade

Board Regular
Joined
Apr 8, 2002
Messages
231
Bless you! I can follow that for selections such as F2:F5000, but pivot tables use a different type of reference. A1:P61545 is R1C1:R61545C16, how do I apply the above to my pivot table:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _"Data!R1C1:R61545C16")

Thanks again!
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Again, it's just part of the string, replace the hard coded value with the variable:
Code:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _"Data!R1C1:R" & NoOfRows & "C16")
 

Forum statistics

Threads
1,140,917
Messages
5,703,168
Members
421,279
Latest member
emzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top