Problem with ROWS in VBA

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
107
Office Version
  1. 365
Platform
  1. Windows
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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,566
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
 

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
107
Office Version
  1. 365
Platform
  1. Windows
The goal of the rest of the code is to hide the selected rows.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
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
 

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
107
Office Version
  1. 365
Platform
  1. Windows
THANKS!! You guys really bailed me out. It worked perfectly.
 

Forum statistics

Threads
1,176,625
Messages
5,904,087
Members
435,072
Latest member
yijiyap

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