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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
197
Alex Blakenburg
A
T
  • Question Question
Multi find and replace part of cell
Replies
8
Views
216
tigerzen
T
P
  • Question Question
Create Equally Distributed Deliverables Table
Replies
4
Views
445
KRice
K
A
  • Solved
Dynamic Grade Look Up using 2 criteria
Replies
5
Views
322
Fluff
Fluff
ExcelNewbie2020
  • Solved
filter unique value and transpose the 3rd column base on unique values
Replies
4
Views
252
Fluff
Fluff

Forum statistics

Threads
1,222,561
Messages
6,166,802
Members
452,073
Latest member
akinch

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