Add block of data at end of unknown range

[NeoFax]

I download every week a file from SAP and the range is different each week. I need to go to the bottom of the range and add 24 rows for each hour of the day and each row in column "D" is the max date of the data. So if in column "D" I have dates from 9/10 - 9/17 i would have 9/17 in the 24 rows and 1am to 24pm. This way I can then make a pivot table that shows all of the employees data on a timescale broken down by day and hour. How would I go about coding this?

 

[P_Guru]

To identify the starting point of your 24 row pasting...

Range("A1:B" & Range("C" & Rows.Count).End(xlUp).Row).Select

Where C is the column in which your imported range has values in every row.

 

[NeoFax]

OK, that selects all of the cells in columns "A:B" that contain data. To select the next rown I would just do a offset? Then I could paste the 24 rows of data. How do I get the last date from column "D"? Max?

Sub setupData()
    Range("A1").End(xlDown).Select
    Selection.Offset(1, 0).Select
    Selection.NumberFormat = "@"
    ActiveCell.FormulaR1C1 = "C9873"
    ActiveCell.Offset(0, 1).Select
    Selection.NumberFormat = "@"
    ActiveCell.FormulaR1C1 = "US01"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "101"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "12:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "1:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "2:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "3:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "4:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "5:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "6:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "7:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "8:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "9:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "10:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "11:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "12:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "13:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "14:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "15:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "16:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "17:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "18:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "19:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "20:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "21:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "22:00"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "23:00"
    Selection.AutoFill Destination:=Range("C2544:C2571"), Type:=xlFillDefault
    Selection.AutoFill Destination:=Range("B2544:B2571"), Type:=xlFillDefault
    Selection.AutoFill Destination:=Range("A2547:A2571"), Type:=xlFillDefault
    Range("H2").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=150
    Range("E2548:E2571").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("F2548").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "#,##0.000"
    ActiveCell.FormulaR1C1 = "0"
    Range("F2549").Select
    Selection.NumberFormat = "#,##0.000"
    ActiveCell.FormulaR1C1 = "0"
    Range("F2550").Select
    Selection.NumberFormat = "#,##0.000"
    ActiveCell.FormulaR1C1 = "0"
    Range("F2548:F2550").Select
    Selection.AutoFill Destination:=Range("F2548:F2571"), Type:=xlFillDefault
    Range("F2548:F2571").Select
    Sheets("September").Select
    Range("G2").Select
    Sheets("Sheet3").Select
    ActiveWindow.ScrollRow = 2529
    Range("G2").Select
    ActiveCell.Formula = "=TIME(HOUR(TIMEVALUE(D2))+1,0,0)"
    Selection.AutoFill Destination:=Range("G2:G2571"), Type:=xlFillDefault
    Range("G2:G2571").Select
    Range("G2547").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveWindow.SmallScroll Down:=-12
    Selection.Copy
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
    ActiveWindow.SmallScroll Down:=69
End Sub

 

[P_Guru]

offset is a bit much, just use +1 in the row identifier.

Then yes, max gives you the latest date.