Having trouble using a nested IF with VBA msgbox

If you want to specify each range like that you are going to need to use the Union property. Very tedious but it will work

If answer1 = vbYes Then
    Dim ws As Worksheet:    Set ws = ThisWorkbook.Sheets("BAPX CALCULATIONS")
    Dim rng As Range
    With ws
        Set rng = Union(.Range("AC7:AC5000"), .Range("AF7:AF5000"), .Range("AI7, AI5000")) 'all your ranges go here
        rng.ClearContents
    End With
End If

Another option would be to incorporate a looping structure if there is consistency amongst your columns. I have given you an example, up to you to make sure it works for your data set

If answer1 = vbYes Then
    For i = 29 To 80 Step 3 'Column AC to CB every 3 columns
        ThisWorkbook.Sheets("BAPX CALCULATIONS").Cells(7, i).Resize(4993, 1).ClearContents
    Next i
    
    For i = 80 To 84 Step 2 'Column CB to CF every 2 columns
        ThisWorkbook.Sheets("BAPX CALCULATIONS").Cells(7, i).Resize(4993, 1).ClearContents
    Next i
    
    For i = 87 To 134 Step 3 'Column CI to ED every 3 columns
        ThisWorkbook.Sheets("BAPX CALCULATIONS").Cells(7, i).Resize(4993, 1).ClearContents
    Next i
End If