Problem using variable name

[Scott JS]

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

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

Second attempt

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

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

 

[VoG]

Try

Range("H” & finalrow).Select

but you do not need to select a range to work with it.

 

[HalfAce]

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:

finalrow = Cells(Rows.Count, "G").End(xlUp).Row

 

[Scott JS]

Thank you, that helped. But now I can’t dynamically name the range using a variable.

Neither

    Range("G622:H" & finalrow).Select
    ActiveWorkbook.Names.Add Name:="dupestemp", RefersToR1C1:= _
        "='sheet'!R622C7:R" & "finalrow" & "C8"

Nor

    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.

 

[Norie]

Scott

You still aren't using the correct syntax.

Try this.

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:

Worksheets("Sheet1").Range("G622:H" & finalrow).Name="dupestemp"

 

[erik.van.geit]

Hi,

you were so close, messing up with double quotes
you can use

Range("G622:H" & finalrow).Select 
    ActiveWorkbook.Names.Add Name:="dupestemp", RefersToR1C1:= _ 
        "='sheet1'!R622C7:R" & finalrow & "C8"

kind regards,
Erik

 

[Scott JS]

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

    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!

 

[Norie]

Scott

That's recorded code?

What manual steps did you take?

 

[Scott JS]

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

 

[Norie]

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.