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

F

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Surname[/TD]
[TD]Forename[/TD]
[TD]UPN[/TD]
[TD]Achievement[/TD]
[TD]Points[/TD]
[TD]Behaviour[/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]123[/TD]
[TD]History[/TD]
[TD]1[/TD]
[TD]PE[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]drama[/TD]
[TD]2[/TD]
[TD]English[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Science[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]History[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Art[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]456[/TD]
[TD][/TD]
[TD][/TD]
[TD]Drama[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Maths[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sociology[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]789[/TD]
[TD]History[/TD]
[TD]1[/TD]
[TD]Pe[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Art[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Drama[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]English[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Geography[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]History[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]IT[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Maths[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PE[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Science[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

What I need is:

[TABLE="width: 500"]
<tbody>[TR]
[TD]rname[/TD]
[TD]Forename[/TD]
[TD]UPN[/TD]
[TD]Achievement[/TD]
[TD]Points[/TD]
[TD]Behaviour[/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]123[/TD]
[TD]History[/TD]
[TD]1[/TD]
[TD]PE[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]123[/TD]
[TD]drama[/TD]
[TD]2[/TD]
[TD]English[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]123[/TD]
[TD][/TD]
[TD][/TD]
[TD]Science[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]123[/TD]
[TD][/TD]
[TD][/TD]
[TD]History[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]123[/TD]
[TD][/TD]
[TD][/TD]
[TD]Art[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]456[/TD]
[TD][/TD]
[TD][/TD]
[TD]Drama[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]456[/TD]
[TD][/TD]
[TD][/TD]
[TD]Maths[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]456[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sociology[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]789[/TD]
[TD]History[/TD]
[TD]1[/TD]
[TD]Pe[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]789[/TD]
[TD][/TD]
[TD][/TD]
[TD]Art[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]789[/TD]
[TD][/TD]
[TD][/TD]
[TD]Drama[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]789[/TD]
[TD][/TD]
[TD][/TD]
[TD]English[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]789[/TD]
[TD][/TD]
[TD][/TD]
[TD]Geography[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]789[/TD]
[TD][/TD]
[TD][/TD]
[TD]History[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]789[/TD]
[TD][/TD]
[TD][/TD]
[TD]IT[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]789[/TD]
[TD][/TD]
[TD][/TD]
[TD]Maths[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]789[/TD]
[TD][/TD]
[TD][/TD]
[TD]PE[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]789[/TD]
[TD][/TD]
[TD][/TD]
[TD]Science[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

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

Thanks
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
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...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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
MickG said:
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.
Click to expand...

Thanks Mick will give this a try but quick question will this copy data from A1 to all the blank cells?
 
Upvote 0
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!!.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Groupby include only counta < 4
Replies
5
Views
214
Alex Blakenburg
A
T
  • Question Question
Multi find and replace part of cell
Replies
8
Views
315
tigerzen
T
P
  • Question Question
Create Equally Distributed Deliverables Table
Replies
4
Views
461
KRice
K
H
  • Solved
Copy non-empty cells from the same column from multiple sheets to a sheet "Breeders"
Replies
6
Views
72
mumps
mumps
A
  • Solved
Dynamic Grade Look Up using 2 criteria
Replies
5
Views
330
Fluff
Fluff

Forum statistics

Threads
1,224,749
Messages
6,180,727
Members
452,995
Latest member
isldboy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top