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  amp       amp  copyFromRow  Select Selection Copy Sheets  Proposal   Select Rows copyToRow  amp       amp  copyToRow  Select ActiveSheet Paste copyToRow   copyToRow   1 Rows copyToRow  amp       amp  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

User · Answer

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

User · Answer

The key is in the quotes around the colon and  amp   i e  rows variable  amp       amp  variable  select  Adapt this   Rows x  amp       amp  y  select   where x and y are your variables   Some other examples that may help you understand  Rows x  amp       amp  x  select   Or  Rows  x 1   amp       x 3   select   Or  Rows  x 2   amp       amp   y-3   select   Hopefully you get the idea

User · Answer

One needs to make sure the space between the variables and   amp   sign  Check the image   Red one showing invalid commands   The correct solution is Dim copyToRow  copyToRow   5       Rows copyToRow  amp   quot   quot   amp  copyToRow  Select

User · Answer

You need to add quotes  VBA is translating  Rows copyToRow  amp       amp  copyToRow  Select    into   Rows 52 52  Select   Try changing   Rows      amp  copyToRow  amp       amp  copyToRow  amp       Select

User · Answer

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  amp       amp  i    Debug Print UBound r  2    r   Rows i    Debug Print UBound r  2    Rows i  Select  End Sub

User · Answer

I solved the problem for me by addressing also the worksheet first   ws rows x  amp       amp  y  Select   without the reference to the worksheet  ws  I got an error

[excel] selecting an entire row based on a variable excel vba

Examples related to excel

Examples related to vba