Macro to copy a data set with unknown number of rows

[EricB9]

I have data in multiple excel workbooks that I am building a macro to bring in to a single workbook. The data is a list of tenants and move-in dates etc. The data covers columns C:X and begins at row 9. The problem is I need the macro to determine how many rows are in the data set so I know how far to copy down. Basically I need to copy cells C9:X??. Any help would be much appreciated. Column D will always have data for the rows that will need to be copied if that helps.

 

[JoeMo]

Try:
Dim lastRow as Long
lastRow = Range("D" & Rows.Count).End(xlUp).Row

Range("C9:X" & lastRow) is the range you want to copy

 

[EricB9]

Thanks for your response. Unfortunately It is copying more than I want it to. I think the problem is that the data I need to copy will be 15 rows or so, then 100 blank rows, then totals at the bottom. Unfortunately this coding is copying everything down through the totals when I only want to copy the top 15+ rows that have the raw data in them. Any ideas would be great.