Problem with ROWS in VBA

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
101
I am trying to write some VBA that will reference two variables, however I get a run-time error when I debug.

Here is the code:

Rows("StartRow:EndRow-1").Select

where Dim StartRow As Integer
and Dim EndRow As Integer

For example, when I run my subroutine, it assigns a value of 2 to StartRow and 4 to EndRow. I can see this in my Locals window. What am I doing wrong?

p.s. I apologize for not posting the entire code, but the IT staff don't let me download any add-ons :(
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,055
Office Version
  1. 365
Platform
  1. Windows
Anything you enclose in quotes in treated as literal text, not variables. Try:

Rows(StartRow & ":" & EndRow-1).Select
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Hi

Your code is being interpreted as text where it falls between the double quotes:

Rows("StartRow:EndRow-1").Select

everything in bold is being seen as text when Excel tries to run the code. What you want to do is have Excel see your variables, therefore they need to be not surrounded by quotes as Vog indicated:

Code:
Rows(StartRow & ":" & EndRow-1).Select

Best regards

Richard
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Code:
Rows(StartRow & ":" & EndRow-1).Hidden = True

better :LOL:
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Also, when dealing with row numbers in variables, it's better to use the Long data type since the amount of rows available in Excel exceeds the range of the Integer data type.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
p.s. I apologize for not posting the entire code, but the IT staff don't let me download any add-ons

You can enclose your code in HTML Code tags.

"["code"]" Code Here "["/code"]"

No quotes. ;)

HTH,

Smitty
 

Forum statistics

Threads
1,141,587
Messages
5,707,238
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top