Using variables in Rows.Select
 

Hi,

I have a procedure where I determine the range of rows I want to hide. I
have tested the values and things are working up to the Rows statement. How
do I use variables in the statement below? I have tried several
combinations and nothing works. Can anyone tell me what I'm missing?

Both variables are Dim as Long

Rows(LastRow&":"& BottomRow).Select
This gives me a compile error, expected list separator or )

Selection.EntireRow.Hidden = True

Thanks

Using variables in Rows.Select
 

This worked for me:

Dim LastRow As Long
Dim BottomRow As Long

LastRow = 12
BottomRow = 8

Rows(LastRow & ":" & BottomRow).EntireRow.Hidden = True



Karen53 wrote:

Hi,

I have a procedure where I determine the range of rows I want to hide. I
have tested the values and things are working up to the Rows statement. How
do I use variables in the statement below? I have tried several
combinations and nothing works. Can anyone tell me what I'm missing?

Both variables are Dim as Long

Rows(LastRow&":"& BottomRow).Select
This gives me a compile error, expected list separator or )

Selection.EntireRow.Hidden = True

Thanks




--

Dave Peterson

Using variables in Rows.Select
 

Hi Dave,

Do the spaces make a difference? I see no difference between your's and
mine other than the spaces. I pasted yours into my procedure and it worked
fine. I commented out your line and added the spaces into mine and it worked.

"Dave Peterson" wrote:

This worked for me:

Dim LastRow As Long
Dim BottomRow As Long

LastRow = 12
BottomRow = 8

Rows(LastRow & ":" & BottomRow).EntireRow.Hidden = True



Karen53 wrote:

Hi,

I have a procedure where I determine the range of rows I want to hide. I
have tested the values and things are working up to the Rows statement. How
do I use variables in the statement below? I have tried several
combinations and nothing works. Can anyone tell me what I'm missing?

Both variables are Dim as Long

Rows(LastRow&":"& BottomRow).Select
This gives me a compile error, expected list separator or )

Selection.EntireRow.Hidden = True

Thanks




--

Dave Peterson

Using variables in Rows.Select
 

For this line...

Rows(LastRow&":"& BottomRow).Select

yes, the lack of a space between LastRow and the ampersand is a problem.
Here is a desciption I posted recently that describes why...

Original Posters message
======================
UserName = InputBox (blah blah blah)
MsgBox("Thank You " & username & " for taking part in this survey")

Make sure you have a blank before and after the &. I've found that
I get errors because there's no blank.

My response
=====================
You only need to provide a space between the variable name and the ampersand
(&) following it. The reason has to do with VB being backward compatible,
syntax-wise, with older VB's and even the DOS versions of BASIC that
preceded it. In the "old days", you could specify a variable's data type by
using suffix characters attached to the end of the variable's name. The
ampersand character happens to be one of those suffix characters. Placing an
ampersand at the end of a variable's name makes the variable a Long data
type... the others being percent sign (%) for Integer, exclamation mark (!)
for Single, pound sign (#) for Double, dollar sign ($) for String and,
although the data type is "newer", the at sign (@) for Currency. Anyway,
VB's problem comes from its considering the second ampersand in this string
(no space between it and the variable name in front of it)...

MsgBox "Thank You "&UserName&" for taking part in this survey"

to be the Long data type suffix character meaning there is no concatenation
symbol for the variable and the text following it. I know, it should be able
to figure out that UserName contains a String value so that it can't be a
Long, but it can't. Anyway, you can see this in action by putting in the
ampersand that VB is missing.

MsgBox "Thank You "&UserName&&" for taking part in this survey"

The above string of text will not raise an error when the above MsgBox
statement is typed in; instead, the automatic spacing that VB does will take
place around the second ampersand once the statement is committed. Now, of
course, you will get a type-declaration error when you attempt to actually
run this code because UserName is (presumably) Dim'med as a String and the
concatenation of a declared Long variable and a String is improper.

Anyway, that is the 'why' of the error you mentioned.

Rick


"Karen53" wrote in message
...
Hi Dave,

Do the spaces make a difference? I see no difference between your's and
mine other than the spaces. I pasted yours into my procedure and it
worked
fine. I commented out your line and added the spaces into mine and it
worked.

"Dave Peterson" wrote:

This worked for me:

Dim LastRow As Long
Dim BottomRow As Long

LastRow = 12
BottomRow = 8

Rows(LastRow & ":" & BottomRow).EntireRow.Hidden = True



Karen53 wrote:

Hi,

I have a procedure where I determine the range of rows I want to hide.
I
have tested the values and things are working up to the Rows statement.
How
do I use variables in the statement below? I have tried several
combinations and nothing works. Can anyone tell me what I'm missing?

Both variables are Dim as Long

Rows(LastRow&":"& BottomRow).Select
This gives me a compile error, expected list separator or )

Selection.EntireRow.Hidden = True

Thanks




--

Dave Peterson

Using variables in Rows.Select
 

Thank you! I was beginning to think I was going nuts!

"Rick Rothstein (MVP - VB)" wrote:

For this line...

Rows(LastRow&":"& BottomRow).Select

yes, the lack of a space between LastRow and the ampersand is a problem.
Here is a desciption I posted recently that describes why...

Original Posters message
======================
UserName = InputBox (blah blah blah)
MsgBox("Thank You " & username & " for taking part in this survey")

Make sure you have a blank before and after the &. I've found that
I get errors because there's no blank.

My response
=====================
You only need to provide a space between the variable name and the ampersand
(&) following it. The reason has to do with VB being backward compatible,
syntax-wise, with older VB's and even the DOS versions of BASIC that
preceded it. In the "old days", you could specify a variable's data type by
using suffix characters attached to the end of the variable's name. The
ampersand character happens to be one of those suffix characters. Placing an
ampersand at the end of a variable's name makes the variable a Long data
type... the others being percent sign (%) for Integer, exclamation mark (!)
for Single, pound sign (#) for Double, dollar sign ($) for String and,
although the data type is "newer", the at sign (@) for Currency. Anyway,
VB's problem comes from its considering the second ampersand in this string
(no space between it and the variable name in front of it)...

MsgBox "Thank You "&UserName&" for taking part in this survey"

to be the Long data type suffix character meaning there is no concatenation
symbol for the variable and the text following it. I know, it should be able
to figure out that UserName contains a String value so that it can't be a
Long, but it can't. Anyway, you can see this in action by putting in the
ampersand that VB is missing.

MsgBox "Thank You "&UserName&&" for taking part in this survey"

The above string of text will not raise an error when the above MsgBox
statement is typed in; instead, the automatic spacing that VB does will take
place around the second ampersand once the statement is committed. Now, of
course, you will get a type-declaration error when you attempt to actually
run this code because UserName is (presumably) Dim'med as a String and the
concatenation of a declared Long variable and a String is improper.

Anyway, that is the 'why' of the error you mentioned.

Rick


"Karen53" wrote in message
...
Hi Dave,

Do the spaces make a difference? I see no difference between your's and
mine other than the spaces. I pasted yours into my procedure and it
worked
fine. I commented out your line and added the spaces into mine and it
worked.

"Dave Peterson" wrote:

This worked for me:

Dim LastRow As Long
Dim BottomRow As Long

LastRow = 12
BottomRow = 8

Rows(LastRow & ":" & BottomRow).EntireRow.Hidden = True



Karen53 wrote:

Hi,

I have a procedure where I determine the range of rows I want to hide.
I
have tested the values and things are working up to the Rows statement.
How
do I use variables in the statement below? I have tried several
combinations and nothing works. Can anyone tell me what I'm missing?

Both variables are Dim as Long

Rows(LastRow&":"& BottomRow).Select
This gives me a compile error, expected list separator or )

Selection.EntireRow.Hidden = True

Thanks




--

Dave Peterson

Using variables in Rows.Select
 

Just another option if you don't like working w/ strings.

Sub Demo()
Dim TopRow As Long
Dim BottomRow As Long

TopRow = 8
BottomRow = 12

Rows(TopRow).Resize(BottomRow - TopRow + 1).Hidden = True
End Sub

--
HTH :)
Dana DeLouis


"Karen53" wrote in message
...
Hi,

I have a procedure where I determine the range of rows I want to hide.
I
have tested the values and things are working up to the Rows statement.
How
do I use variables in the statement below? I have tried several
combinations and nothing works. Can anyone tell me what I'm missing?

Both variables are Dim as Long

Rows(LastRow&":"& BottomRow).Select
This gives me a compile error, expected list separator or )

Selection.EntireRow.Hidden = True

Thanks