Please help me remove unrequired .Select from my code

AusSteelMan

Board Regular
Joined
Sep 4, 2009
Messages
208
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
Code:
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
Code:
Sheets("Score Sheet").Select
before
Code:
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
If you want the Range to pick up the With statement, you need to start with a period:

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

Bill
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Ok. I just suffered a cross-post. I will look at what you just posted and reply.

Thanks,
Darren
 
Last edited:
Upvote 0
So the code with 3 periods worked perfectly.

The next step (not previously posted) is:
Code:
    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
 
Upvote 0

Forum statistics

Threads
1,223,054
Messages
6,169,834
Members
452,284
Latest member
TKM623

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top