VBA to copy/paste down values and stop at row with text

[lukestkd]

Hi all.
File attached with dummy data.

I have an export from our school data system with class codes/teacher and students.

When it exported the data it only put the class code/teacher and ID on one row, then listed the students. I need the class code, teacher and ID on every row.

So what I'm trying to do is to get:
Copy the value in A2, B2, C2, E2, F2 and paste down in the empty cells the rows below

STOP pasting when it reaches the row that has text (and the information from the next class) in this case that is row 26

Then copy the values in A26, B26, C26, E26, F26 and paste down in the empty cells the rows below

- Repeat that cycle throughout the sheet


Classes vary in size so I can't say it will always be X number of rows to paste to.

Is there way to set this up to do this? The report will be running frequently and there are over 1000 classes so manually isn't a viable option.

I'd really appreciate any support you can give.

Thanks

Luke

[Norie]

Select columns A to F, then goto Find & Select...Goto Special and select Blanks.

Then in the formula bar put =A2 and confirm with CTRL+ENTER.

Code.

With Range("A:F")
    .SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
    .CurrentRegion.Value = .CurrentRegion.Value
End With

[PCI]

See next code

Option Explicit

Sub Treat()
Dim WkRg  As Range
Dim F  As Range
    Set WkRg = Range("A2:F" & Range("D" & Rows.Count).End(xlUp).Row)
    WkRg.Select
    For Each F In WkRg
        If F = "" Then F = F.Offset(-1, 0)
    Next
End Sub

[MickG]

Try this:-

Sub MG26Jun45
Dim Rng As Range, Dn As Range, T As Variant, T1 As Variant
Set Rng = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
For Each Dn In Rng
    If Dn.Offset(, -3) > "" Then
        T = Application.Index(Dn.Offset(, -3).Resize(, 6), Array(1, 2, 3))
        T1 = Application.Index(Dn.Offset(, -3).Resize(, 6), Array(5, 6))
    Else
        Dn.Offset(, -3).Resize(, 3) = T
        Dn.Offset(, 1).Resize(, 2) = T1
    End If
Next Dn
End Sub

Regards Mick