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

Fazila

Board Regular
Joined
Nov 19, 2014
Messages
163
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

SurnameForenameUPNAchievementPointsBehaviourPoints
AB123History1PE5
drama2English3
Science10
History1
Art6
CD456Drama2
Maths1
Sociology4
EF789History1Pe1
Art2
Drama3
English4
Geography5
History6
IT7
Maths8
PE9
Science10

<tbody>
</tbody>

What I need is:

rnameForenameUPNAchievementPointsBehaviourPoints
AB123History1PE5
AB123drama2English3
AB123Science10
AB123History1
AB123Art6
CD456Drama2
CD456Maths1
CD456Sociology4
EF789History1Pe1
EF789Art2
EF789Drama3
EF789English4
EF789Geography5
EF789History6
EF789IT7
EF789Maths8
EF789PE9
EF789Science10

<tbody>
</tbody>

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

Thanks
 

Fazila

Board Regular
Joined
Nov 19, 2014
Messages
163
This is what I am using at the moment (quite convoluted as you can see)

Code:
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-questions/660608-macro-copy-cell-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.
 
Last edited:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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

Board Regular
Joined
Nov 19, 2014
Messages
163
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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!!.
 

Forum statistics

Threads
1,081,969
Messages
5,362,484
Members
400,677
Latest member
champchamp

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top