copy cells from columns A to C to next non-blank cell

[Fazila]

Another day another plea for help

I have a spreadsheet with 255,398 rows. Each student has anything between 5 to a 1000 rows, however, not each row has the student's details. So the table (in a very simplified form) looks something like this

Surname	Forename	UPN	Achievement	Points	Behaviour	Points
A	B	123	History	1	PE	5
			drama	2	English	3
					Science	10
					History	1
					Art	6
C	D	456			Drama	2
					Maths	1
					Sociology	4
E	F	789	History	1	Pe	1
					Art	2
					Drama	3
					English	4
					Geography	5
					History	6
					IT	7
					Maths	8
					PE	9
					Science	10

<tbody>
</tbody>

What I need is:

rname	Forename	UPN	Achievement	Points	Behaviour	Points
A	B	123	History	1	PE	5
A	B	123	drama	2	English	3
A	B	123			Science	10
A	B	123			History	1
A	B	123			Art	6
C	D	456			Drama	2
C	D	456			Maths	1
C	D	456			Sociology	4
E	F	789	History	1	Pe	1
E	F	789			Art	2
E	F	789			Drama	3
E	F	789			English	4
E	F	789			Geography	5
E	F	789			History	6
E	F	789			IT	7
E	F	789			Maths	8
E	F	789			PE	9
E	F	789			Science	10

<tbody>
</tbody>

is there a quick way this can be done? Any help as always is very much appreciated.

Thanks

 

[Fazila]

This is what I am using at the moment (quite convoluted as you can see)

Sub FillUPN()
    Dim Name As String
    For Each c In Range("B9097:B" & Cells(Rows.Count, 2).End(xlUp).Row)
        If Cells(c.Row, 3) > "" Then
            Name = Cells(c.Row, 3).Value
            Cells(c.Row, 3).Copy
        Else
            Cells(c.Row, 3).Value = Name
        End If
    Next
End Sub


Sub FillSurname()
    Dim Name As String
    For Each c In Range("B9097:B" & Cells(Rows.Count, 2).End(xlUp).Row)
        If Cells(c.Row, 2) > "" Then
            Name = Cells(c.Row, 2).Value
            Cells(c.Row, 2).Copy
        Else
            Cells(c.Row, 2).Value = Name
        End If
    Next
End Sub


Sub FillForename()
    Dim Name As String
    For Each c In Range("B9097:B" & Cells(Rows.Count, 2).End(xlUp).Row)
        If Cells(c.Row, 1) > "" Then
            Name = Cells(c.Row, 1).Value
            Cells(c.Row, 1).Copy
        Else
            Cells(c.Row, 1).Value = Name
        End If
    Next
End Sub

Thanks to this thread https://www.mrexcel.com/forum/excel...ell-value-down-until-next-non-blank-cell.html

However, it would be nice if I could marry them all up so it would be one macro rather than 3.

 

[MickG]

Try this:-
Fill Blank Cell Down Column
1. Select all of the data in column A to C, from top to bottom.
2. Click on Home->Find & Select->Go To Special
3. Click "Blanks" and hit "Ok". (At this point, all blanks in the column should be selected, and cell A2 should be the active cell.)
4. Press "=", and then press up to select cell A1.
5. Press CtrlEnter to copy the formula into all selected cells.

 

[Fazila]

Try this:-
Fill Blank Cell Down Column
1. Select all of the data in column A to C, from top to bottom.
2. Click on Home->Find & Select->Go To Special
3. Click "Blanks" and hit "Ok". (At this point, all blanks in the column should be selected, and cell A2 should be the active cell.)
4. Press "=", and then press up to select cell A1.
5. Press CtrlEnter to copy the formula into all selected cells.

Thanks Mick will give this a try but quick question will this copy data from A1 to all the blank cells?

 

[MickG]

I tried this by selecting all the blank cells relating to your data, in column "A to C", following the instructions, and it worked for me!!.

 

[Fazila]

Thank you MickG that worked beautifully

 

[MickG]

You're welcome