2
\$\begingroup\$

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
\$\endgroup\$
  • 3
    \$\begingroup\$ 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! \$\endgroup\$ – Mathieu Guindon Mar 8 '19 at 17:58
  • 3
    \$\begingroup\$ @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. \$\endgroup\$ – J Doe Mar 8 '19 at 18:03
  • 1
    \$\begingroup\$ 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? \$\endgroup\$ – FreeMan Mar 8 '19 at 18:26
  • \$\begingroup\$ @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. \$\endgroup\$ – J Doe Mar 8 '19 at 18:59
  • 1
    \$\begingroup\$ Named ranges can be your friend. Your code will then become <Qualified>.Range("MyNamedRange").Value = <Qualified>.Range("MyNamedRange").Value \$\endgroup\$ – AJD Mar 8 '19 at 23:06
1
\$\begingroup\$

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)

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.