# Optimizing VBA Select Code

I'm trying to reword the code below to avoid using the Select and Selection terms. I tried to Range("S"...).Copy instead of .Select but that didn't work for some reason. Does anyone know a way I can reword this to avoid Select and Selection statements and why my change .Select to .Copy wouldn't have worked?

The code below looks in Column A for the identifiers Tango and Alpha. Then it uses those identifiers to figure out the range between rows to select in Column S and copies and pastes them as values. I can confirm the code does run as is.

Sub copy_paste()
Dim wb As Workbook
Dim ws As Worksheet
Dim FoundCell1 As Range
Dim FoundCell2 As Range
Set wb = ActiveWorkbook
Set ws = ActiveSheet

Const WHAT_TO_FIND1 As String = "Tango"
Set FoundCell1 = ws.Range("A:A").Find(What:=WHAT_TO_FIND1)

Const WHAT_TO_FIND2 As String = "Alpha"
Set FoundCell2 = ws.Range("A:A").Find(What:=WHAT_TO_FIND2)

Range("S" & FoundCell1.Row + 1 & ":S" & FoundCell2.Row - 1).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End sub

• Welcome to CR! It's not clear whether the code you're presenting is working or not... and since code that doesn't work as intended isn't ready for a peer review and is therefore off-topic on this site, reviewers are more likely to vote to close and move on - please edit your post to clarify, and note that you'll get much better/thorough feedback if you include the whole procedure, rather than just a snippet taken out of its context. I really hope you edit & clarify - there's a lot of enhancement opportunities (and hidden bugs) in there! Mar 8, 2019 at 17:58
• @MathieuGuindon Hi Mathieu, thank you for your feedback. I've edited the post the clarify what the code does and can confirm that it does run. Mar 8, 2019 at 18:03
• Why are you doing the .PasteSpecial? Are Tango and Alpha (or the rows between them) the result of formulas and you're trying to overwrite the formulas with the actual values? Mar 8, 2019 at 18:26
• @FreeMan The .PasteSpecial is just from me recording my macro and that's what it spit out. Tango and Alpha are the unique identifiers that are located on the row numbers I want to pull. The rows can shift so that's why I have those there so that it can dynamically determine between which rows to perform on. Mar 8, 2019 at 18:59
• Named ranges can be your friend. Your code will then become <Qualified>.Range("MyNamedRange").Value = <Qualified>.Range("MyNamedRange").Value
– AJD
Mar 8, 2019 at 23:06

To start with: You are currently not using the wb Workbook object after setting it. Is it needed in other code, or can it be removed?

If you are just trying to 'flatten' the range to get rid of any formula, you can just use <Range>.Value = <Range>.Value, instead of Copy / PasteSpecial

Range("S" & FoundCell1.Row + 1 & ":S" & FoundCell2.Row - 1).Select
'Selection.Copy
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Value = Selection.Value


You can also use a With Statment to replace the requirement for a Select or Selection.

'Range("S" & FoundCell1.Row + 1 & ":S" & FoundCell2.Row - 1).Select
With w.Range("S" & FoundCell1.Row + 1 & ":S" & FoundCell2.Row - 1)
.Value = .Value
End With
'Selection.Copy
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


And, this is personal preference, but you could use .Offset, .EntireRow and .Column to define the range instead of building a text string address:

With ws.Range(FoundCell1.Offset(1, 0), FoundCell2.Offset(-1, 0)).EntireRow.Columns("S")
.Value = .Value
End With


(This only requires "Column S" to be defined once, in case you need to change it later - the other option would be to have it in a Const at the start of the Sub)

As yet-another-alternative, you could use WorksheetFunction.Match to retrieve the Rows directly (TangoRow = WorksheetFunction.Match(WHAT_TO_FIND1, ws.Columns("A"), 0) would give the same result as FoundCell1.Row)