Mass populating cells

[mattyn]

I have a worksheet that records Students and their progress through courses.


Each course has some modules, each module has some Training objectives (TO), each TO has some Enabling Objectives (EO) and each EO has some Key Learning Points (KLPs).

Each row starts with student name (col B), and as it moves right, has a cell for each KLP that I want to annotate with a Y or N depending on whether the student has completed it or not. As such, a nice wide spreadsheet.

There are a lot of KLPs - and at the end of instruction, I need the instructors to annotate the worksheet that all students have received all KLPs. If I can I want to do this in one click.​

So I type the cell range in the name box, then type Y and ctrl-enter - which fills all the cells in the range with a "Y". I have made this a macro and assigned that to a button. All good - it fills each cell in that range with a Y - and if the answer should be N - the instructor can over type this quite easily. I am happy so far!

However, the table has up to 12 students - but I don't always have 12 students in a class. I want the macro to ignore rows where there is no student name.

I hope I have explained this adequately - if so, is what I want to do possible?

The macro is as follows - currently the range of cells is J10:M21

Sub Macro1()
'
' Macro1 Macro
'
'
Application.Goto Reference:="R10C10:R21C13"
Selection.FormulaR1C1 = "Y"
End Sub


Many thanks

Matt

 

[Peter_SSs]

If you are just targeting rows 10:21 then try (in a copy of your workbook)

Sub FillCells()
  Intersect(Range("B10:B21").SpecialCells(xlConstants).EntireRow, Columns("J:M")).Value = "Y"
End Sub

If you want all rows from 10 down that have names in column B then try

Sub FillCells()
  Intersect(Range("B10", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).EntireRow, Columns("J:M")).Value = "Y"
End Sub

 

[mattyn]

Thank you Peter - will that work with the existing macro & button?

I suppose what I mean is - where do i put that?

 

[Peter_SSs]

Thank you Peter - will that work with the existing macro & button?

I suppose what I mean is - where do i put that?

If the "existing macro" is the one you posted, then just replace the 2 lines of code in the body of that macro with the one line of code in the body of mine.

 

[mattyn]

No worries Peter - solved it thank you

works a treat!

 

[Peter_SSs]

No worries Peter - solved it thank you

works a treat!

You're welcome.