CODEWITHSUNDEEP
excelvba
thebiglebowski11
thebiglebowski11

Reputation: 1461

selecting an entire row based on a variable excel vba

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

Upvotes: 3

Views: 146108

Answers (7)

Aneesh R P Prakkulam
Aneesh R P Prakkulam

Reputation: 219

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

Upvotes: 0

Masood Salik
Masood Salik

Reputation: 139

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

Error

The correct solution is

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

Upvotes: 0

Felix
Felix

Reputation: 31

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.

Upvotes: 3

Travis
Travis

Reputation: 11

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.

Upvotes: 1

John Noel
John Noel

Reputation: 21

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

Upvotes: 2

assylias
assylias

Reputation: 328618

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

Upvotes: 4

kirbs
kirbs

Reputation: 168

You need to add quotes. VBA is translating

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

into

 Rows(52:52).Select

Try changing 

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

Upvotes: 0

Related Questions