Problem using variable name

Scott JS

New Member
Joined
Aug 3, 2005
Messages
35
In attempting to write an VBA marco in excel, both of the two pieces of code below return

Run-time error ‘1004’:
Method “Range” of object “_Global” failed

With the second line of code highlighted.

First attempt

Code:
    finalrow = Range("G65536").End(xlUp).Row
    Range("H & finalrow").Select

Second attempt

Code:
    finalrow = Range("G65536").End(xlUp).Row
    Range("H” & “finalrow").Select

I have "dim'ed" finalrow as an integer. What am I doing wrong?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Try

Range("H” & finalrow).Select

but you do not need to select a range to work with it.
 
Upvote 0
I have "dim'ed" finalrow as an integer. What am I doing wrong?
When 'dimming' the data type for rows you want to dim them as a data
type Long instead of Integer.
The data type Integer will only work for numbers (or in your case rows) ranging from
-32,768 through 32,767 whereas the data type Long will work for numbers ranging from
-2,147,483,648 through 2,147,483,647.

Also, to keep your code compatible with the latest (& past/future) version(s) of excel, you
may want to consider not hard coding the last row in the sheet as 65536. The new version
has already exceeded a million rows per sheet so 65536 won’t get you anywhere
near the bottom.
A more flexible syntax would be:
Code:
finalrow = Cells(Rows.Count, "G").End(xlUp).Row
 
Upvote 0
Thank you, that helped. But now I can’t dynamically name the range using a variable.

Neither
Code:
    Range("G622:H" & finalrow).Select
    ActiveWorkbook.Names.Add Name:="dupestemp", RefersToR1C1:= _
        "='sheet'!R622C7:R" & "finalrow" & "C8"
Nor
Code:
    Range("G622:H" & finalrow).Select
    ActiveWorkbook.Names.Add Name:="dupestemp", RefersToR1C1:= _
        "=’sheet1’!R622C7:R & finalrow & C8"
Ends up naming the range – which I need for subsequent lookups.
 
Upvote 0
Scott

You still aren't using the correct syntax.

Try this.
Code:
Range("G622:H" & finalrow).Name="dupestemp"
Note since there is no worksheet reference this will create the named range on the active sheet.

If you want to specify the sheet:
Code:
Worksheets("Sheet1").Range("G622:H" & finalrow).Name="dupestemp"
 
Upvote 0
Hi,

you were so close, messing up with double quotes
you can use
Code:
Range("G622:H" & finalrow).Select 
    ActiveWorkbook.Names.Add Name:="dupestemp", RefersToR1C1:= _ 
        "='sheet1'!R622C7:R" & finalrow & "C8"
kind regards,
Erik
 
Upvote 0
Thanks for all your help. My last question is how to code the "hiding of grouped coolumns. what i get when I do it in recorder mode bombs when i try to run it

Code:
    ActiveCell.Range("A1:E1").Select
    ExecuteExcel4Macro "SHOW.DETAIL(2,6,FALSE,,1)"
    ActiveCell.Offset(0, 6).Range("A1:K1").Select
    ExecuteExcel4Macro "SHOW.DETAIL(2,18,FALSE,,7)"

Thanks again for all the help!
 
Upvote 0
Scott

That's recorded code?

What manual steps did you take?
 
Upvote 0
I went to the first column of a grouped section of columns (that were visible), highlighted the 5 columns that need to be hidden, then went to Data -- Group -- Hide detail

Then cursored over to next set of columns that were already grouped (but visible) and went through the same process.

If I just click on the - (minus) sign above the column to the right of the grouped section, the recorder records nothing
 
Upvote 0
Scott

Could you post some sample data?

The reason I'm curious about this is because of the ExecuteExcel4Macro part of the code.

I don't think I've ever seen that as part of recorded code.
 
Upvote 0

Forum statistics

Threads
1,217,681
Messages
6,137,951
Members
450,100
Latest member
mscetr

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
Back
Top