selecting an entire row based on a variable excel vba

Question

I am trying to select an entire row in a different sheet and then copy the row to the sheet I am currently in with a macro. The code works fine if the Rows() sub is passed integers hardcoded but when I put a variable I get the "Select method of Range class failed" error. here is the code I have:

Sheets("BOM").Select
Rows(copyFromRow & ":" & copyFromRow).Select
Selection.Copy
Sheets("Proposal").Select
Rows(copyToRow & ":" & copyToRow).Select
ActiveSheet.Paste
copyToRow = copyToRow + 1
Rows(copyToRow & ":" & copyToRow).Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

if instead i used :

Rows("52:52").Select
Selection.Copy

it works fine, but when the variable is there, the error occurs.

Thanks

Have you checked the value of copyFromRow in debugging mode? Is it 52? Also if copyFromRow is a number, you can simply use Rows(copyFromRow). — assylias
– assylias, Commented May 14, 2012 at 16:08
yes it is the correct value, and i tried both ways, neither works — thebiglebowski11
– thebiglebowski11, Commented May 14, 2012 at 17:57

assylias · Accepted Answer · 2012-05-14 18:12:04Z

4

I just tested the code at the bottom and it prints 16384 twice (I'm on Excel 2010) and the first row gets selected. Your problem seems to be somewhere else.

Have you tried to get rid of the selects:

Sheets("BOM").Rows(copyFromRow).Copy
With Sheets("Proposal")
    .Paste Destination:=.Rows(copyToRow)
    copyToRow = copyToRow + 1
    Application.CutCopyMode = False
    .Rows(copyToRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End With

Test code to get convinced that the problem does not seem to be what you think it is.

Sub test()

  Dim r
  Dim i As Long

  i = 1

  r = Rows(i & ":" & i)
  Debug.Print UBound(r, 2)
  r = Rows(i)
  Debug.Print UBound(r, 2)
  Rows(i).Select

End Sub

edited May 14, 2012 at 18:12

answered May 14, 2012 at 18:02

assylias

330k84 gold badges680 silver badges806 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zx485 · Accepted Answer · 2018-08-24 22:10:05Z

3

I solved the problem for me by addressing also the worksheet first:

ws.rows(x & ":" & y).Select

without the reference to the worksheet (ws) I got an error.

edited Aug 24, 2018 at 22:10

zx485

29.1k28 gold badges55 silver badges65 bronze badges

answered Aug 24, 2018 at 21:16

Felix

311 bronze badge

Comments

Wai Ha Lee · Accepted Answer · 2015-04-08 18:15:26Z

2

Saw this answer on another site and it works for me as well!

Posted by Shawn on October 14, 2001 1:24 PM

var1 = 1

var2 = 5

Rows(var1 & ":" & var2).Select

That worked for me, looks like you just have to keep the variables outside the quotes and add the and statement (&)

-Shawn

edited Apr 8, 2015 at 18:15

Wai Ha Lee

8,856102 gold badges61 silver badges100 bronze badges

answered Apr 8, 2015 at 17:33

John Noel

211 bronze badge

Comments

Travis · Accepted Answer · 2016-11-10 17:58:50Z

1

The key is in the quotes around the colon and &, i.e. rows(variable & ":" & variable).select

Adapt this:

Rows(x & ":" & y).select

where x and y are your variables.

Some other examples that may help you understand

Rows(x & ":" & x).select

Or

Rows((x+1) & ":" (x*3)).select

Or

Rows((x+2) & ":" & (y-3)).select

Hopefully you get the idea.

answered Nov 10, 2016 at 17:58

Travis

111 bronze badge

Comments

kirbs · Accepted Answer · 2012-05-14 16:43:05Z

0

You need to add quotes. VBA is translating

Rows(copyToRow & ":" & copyToRow).Select`

into

 Rows(52:52).Select

Try changing

Rows(""" & copyToRow & ":" & copyToRow & """).Select

answered May 14, 2012 at 16:43

kirbs

1681 silver badge8 bronze badges

1 Comment

assylias Over a year ago

No, copyToRow & ":" & copyToRow is already a string. What you propose will throw an error.

Masood Salik · Accepted Answer · 2020-11-23 16:47:01Z

0

One needs to make sure the space between the variables and '&' sign. Check the image. (Red one showing invalid commands)

The correct solution is

Dim copyToRow: copyToRow = 5      
Rows(copyToRow & ":" & copyToRow).Select

edited Nov 23, 2020 at 16:47

answered Nov 23, 2020 at 16:30

Masood Salik

1391 gold badge1 silver badge11 bronze badges

Comments

Aneesh R P Prakkulam · Accepted Answer · 2021-12-08 18:00:24Z

0

mycell = ActiveCell
r = mycell.Row
c = mycell.Column
Range(Cells(r, c), Cells(r + 100, c)).Select

answered Dec 8, 2021 at 18:00

Aneesh R P Prakkulam

2273 silver badges7 bronze badges

Collectives™ on Stack Overflow

selecting an entire row based on a variable excel vba

7 Answers 7

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related