Incorrect syntax

[AMAS]

Hi,

I can't seem to get this syntax correct. I am trying to select a range in the temporary sheet based on the range with data in column A in the original sheet.

Range("A2", Sheets("Original").Range("A" & Rows.Count).End(xlUp)).Select

Anybody see where my mistake is?

AMAS

 

[VoG]

Perhaps

Range("A2", Range("A" & Sheets("Original").Range("A" & Rows.Count).End(xlUp).Row)).Select

 

[AMAS]

Hi Peter,

Thanks. Works like a charm.

AMAS

 

[Peter_SSs]

Also the slightly more compact ..

Range("A2:A" & Sheets("Original").Range("A" & Rows.Count).End(xlUp).Row).Select

 

[AMAS]

Hi Peter,

One small glitch. When I run this when the Temp sheet is selected it works fine. When another sheet is selected it gives me an Application or Object-defined error (1004).

Here is the code:

Sub Newsub()
    With Sheets("Temp")
        .Range("A2", Range("A" & Sheets("Original").Range("A" & Rows.Count).End(xlUp).Row)).Select
    End With
End Sub

What do you think?

AMAS

 

[Peter_SSs]

Hi Peter,

One small glitch. When I run this when the Temp sheet is selected it works fine. When another sheet is selected it gives me an Application or Object-defined error (1004).

Here is the code:

Sub Newsub()
    With Sheets("Temp")
        .Range("A2", [SIZE="5"][COLOR="Red"][B].[/B][/COLOR][/SIZE]Range("A" & Sheets("Original").Range("A" & Rows.Count).End(xlUp).Row)).Select
    End With
End Sub

What do you think?

AMAS

You need another . as I've shown in your code above. Or

With Sheets("Temp")
    .Range("A2:A" & Sheets("Original").Range("A" & Rows.Count).End(xlUp).Row).Select
End With

 

[AMAS]

Thanks Peter. I will give it a run.

AMAS

 

[AMAS]

Thanks again Peter. Just tested it and it works great. Its funny how sometimes something as small as a dot can have such a big influence on the code.

AMAS