Execute Copy & Paste whilst a conditon is met on variable length data

[gent70map]

The problem I am trying to solve is to write a macro, that copies their name against each course they attend. I have over 600 students, and the student names change on a monthly basis. There is always a space between the last course and next student name. Any pointers welcomed!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]Student[/TD]
[TD]Course[/TD]
[/TR]
[TR]
[TD]Helen Smith[/TD]
[TD]History[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]English[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Italian[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Comb. Sciences[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rob Jones[/TD]
[TD]Italian[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wendy Jones[/TD]
[TD]English[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jon Smith[/TD]
[TD]English [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]History[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Italian[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

Here is a simple way to do it without using any loops:

Sub MyPopulateColumn()

    Dim lastRow As Long
    
'   Find last row in column B with data
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Select all blank rows in column A and popualte formula
    Range("A2:A" & lastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[1]<>"""",R[-1]C,"""")"

'   Change column A to hard-coded values
    Range("A2:A" & lastRow).Value = Range("A2:A" & lastRow).Value
    
End Sub

 

[gent70map]

Welcome to the Board!

Here is a simple way to do it without using any loops:

Sub MyPopulateColumn()

    Dim lastRow As Long
    
'   Find last row in column B with data
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Select all blank rows in column A and popualte formula
    Range("A2:A" & lastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[1]<>"""",R[-1]C,"""")"

'   Change column A to hard-coded values
    Range("A2:A" & lastRow).Value = Range("A2:A" & lastRow).Value
    
End Sub

Hi Joe, Thank you, I would not have got this and was looking at Loops. So cant thank you enough, I will study the code and hopefully understand enough to be able to re use again. This is very inspiring!!

 

[Joe4]

You could use loops to do it, but it isn't nearly as efficient (loops tend to be slow and inefficient).

Basically, the key was to use the technique described at the top of this link here: https://www.extendoffice.com/documents/excel/771-excel-fill-blank-cells-with-value-above.html
If you turn on the Macro Recorder and record yourself doing it manually, you will get the code that does that part (select blank rows).

And if you record yourself entering in the formula that checks column B in the same row to see if it is blank, and if not, copy the value from the cell above, i.e.

=IF(B3<>"",A2,"")

you will get what that formula looks like in FormulaR1C1 format.

I then just combined those two pieces together to create the middle command. The last section simply copies over my range with the corresponding values to get rid of all the formulas.

And at the beginning, I included code to dynamically find the last populated row in a specified column (B).

Hope that helps a little!