Please help me remove unrequired .Select from my code

[AusSteelMan]

Hi everyone,

As I learn more, I am trying to streamline some pieces of code. I am currently working on .Select code.

I have this extract of code: (let me know if I'm better off posting the whole code)
Note: the red comments are ones I have added into the post

Sheets("FirstSheetBeforePlayers").Visible = True
    Sheets("MustBeLastSheetAfterPlayers").Visible = True
    Sheets("P (0)").Visible = True
    Sheets("P (0)").Copy Before:=Sheets("MustBeLastSheetAfterPlayers") [COLOR=red]'<--- this new worksheet becomes the active sheet[/COLOR]
    Sheets("P (0)").Visible = False
    Sheets("FirstSheetBeforePlayers").Visible = False
    Sheets("MustBeLastSheetAfterPlayers").Visible = False
    ActiveSheet.Name = Me.txtPlayerName.Value
    Cells(1, 2) = Me.txtPlayerName
    Cells(3, 2) = Me.txtStartRound
    NewName = Me.txtPlayerName.Value
    StartRound = Me.txtStartRound.Value
 
'Sheets("Score Sheet").Select  [COLOR=red]<---- I removed this one easily enough due to the With Sheets that already existed.[/COLOR]
 
    With Sheets("Score Sheet")         
         r = 13
        While .Cells(r, 2) <> "" ' this finds the next blank line on the Leaderboard
            r = r + 1
        Wend
 
        .Cells(r, 1) = "=RANK(D" & r & ",D$13:D$43,0)"
        .Cells(r, 2) = NewName 
        .Cells(r, 4) = "=SUM(E" & r & ":AG" & r & ")" 
        .Cells(r, 5) = "=IF(AK" & r & "=$B$8,(AK$9-('Info & Settings'!$B$2)),AK" & r & ")"
 
'more code here
 
        .Cells(r, 70) = "=SUM(R" & r & ":AD" & r & ")" ' builds formula to Sum rounds 14 to 26 in Col BR
 
'Sheets("Score Sheet").Select 
'If StartRound > 1 Then
'    Range(Cells(r, 37), Cells(r, (37 + StartRound - 2))).Select
'    Selection.ClearContents
'    Range(Cells(r, 5), Cells(r, (5 + StartRound - 2))).Select
'    Selection.ClearContents
'End If
[COLOR=red]'All of this was replaced with[/COLOR]    
 
If StartRound > 1 Then
    Range(Cells(r, 37), Cells(r, (37 + StartRound - 2))).ClearContents
    Range(Cells(r, 5), Cells(r, (5 + StartRound - 2))).ClearContents
End If
End With

but I found if I don't have

Sheets("Score Sheet").Select

before

If StartRound > 1 Then
    Range(Cells(r, 37), Cells(r, (37 + StartRound - 2))).ClearContents
    Range(Cells(r, 5), Cells(r, (5 + StartRound - 2))).ClearContents
End If

then it does not work. It tries to ClearContents from the active sheet, which is the sheet that was copied in the top section of the code.

I figured since I was still inside the With statement, that it should remove it from that sheet, not the active sheet.

I tried a couple of things that didn't work.

Can anybody tell me:
1. why the With statement doesn't pick up the If statement.
2. Does this mean I really do need the .Select

With the Sheets("Score Sheet").Select there, the code executes properly. So this exercise is about efficiency. Any help/guidance is much appreciated.

Regards and thanks,
ASM

 

[MrExcel]

If you want the Range to pick up the With statement, you need to start with a period:

.Range(....)....

Bill

 

[MrExcel]

You are actually going to need three periods per line...
One before .Range, and then one before each .Cells

With Worksheets("...")
If StartRound > 1 Then
.Range(.Cells(r, 37), .Cells(r, (37 + StartRound - 2))).ClearContents
.Range(.Cells(r, 5), .Cells(r, (5 + StartRound - 2))).ClearContents
End If
End With

I might be inclined to go ahead and leave the Select in there.

Bill

 

[AusSteelMan]

Bill,

Thanks for your reply (and I feel honoured to get a response from MrExcel himself!)

I did try using a period in front of the range code, but I think I must have messed up the If / End If part.

I will try again.

Maybe I can remove the the test by ensuring that the selected range will not actually ClearContents.

Thanks again.

Darren

 

[AusSteelMan]

Ok. I just suffered a cross-post. I will look at what you just posted and reply.

Thanks,
Darren

 

[AusSteelMan]

So the code with 3 periods worked perfectly.

The next step (not previously posted) is:

    Cells(r, 2).Select
    Call SetHyperlink(NewName)

So if I move the End With past this code and modify with periods, it does not work.

BUT, I figure since I need to end up on the Score Sheet anyway, at some stage I need to .Select it. So I am going to leave the End With where it was, .Select the sheet, then select the cell and run the Call.

Thanks again. I have learned more yet again from this great forum.

Darren