Use of Indirect command in Excel macro

Factual Questions
1

So here’s a question for a very particular sort of nerd…

I have a spreadsheet for teachers that lets students enter in their lunch choices for the day. Column A has student names, columns B-D have possible choices.

The spreadsheet has five relevant worksheets (one per day of the week), and I have a macro that goes to each page and clears cells B5-D19 (for example, for a class with 15 students).

Worksheet “Master” contains cell A1, in which the total number of students in a class is listed.

I’d like to write the macro such that it deletes cells starting in B5 and going to D(4+contents of cell Master!A1). I think the indirect command would do that, but I’m not sure exactly how to use it.

Does anyone have any thoughts for me? It’ll need to do it on worksheets Monday, Tuesday, Wednesday, Thursday, and Friday.

(Note that I’m completely self-taught on Excel, meaning I can do some cool stuff but have massive areas of ignorance in the program; if there’s an easier/more elegant way to handle this, I’m all ears.)

Thanks!
Daniel

2

Simplify it to having one worksheet for the students lunch selections for the week
Put the available menu choices per day in a second worksheet and set them as named ranges. (Allow blank as an option)

In first sheet have students in column A, days of week in B-F
Use validation to control menu selection for each days .
You can have one macro to clear the entries as you do now.

If you want to know how many peanut butter sandwiches are required for Wednesday you could put a pivot table, or if the menu selections are fixed use countif.

3

This will work:



Sub ClearDailySheets()
    
    Dim iNumrows As Long, ws As Worksheet
 
    iNumrows = Range("Master!A1")
    
    Set ws = Worksheets("Monday")
    ws.Activate
    ws.Range(Cells(5, 2), Cells(iNumrows + 4, 4)).ClearContents
    
    Set ws = Worksheets("Tuesday")
    ws.Activate
    Worksheets("Tuesday").Range(Cells(5, 2), Cells(iNumrows + 4, 4)).ClearContents
    
    Set ws = Worksheets("Wednesday")
    ws.Activate
    Worksheets("Wednesday").Range(Cells(5, 2), Cells(iNumrows + 4, 4)).ClearContents
    
(etc)

End Sub

The Cells property (of the Range object) is very handy, because you can address cells by row and column. The construct Range(Cells(R1,C1), Cells(R2,C2) refers to an area on the active worksheet.