Replace Rows("2:48").Select to Rows(Range)

[FJSeminario]

I want to be able to select a range of rows to sort, but I don't know the
number of rows in the Sheet, so I find out programmatically first and then
move to the Range object Frm_sortRange01
I am getting a type mistmatch where it is indicated by the Comment
on the right of the statement

Option Explicit
Option Base 1

Dim GeoCodeTableStartRow As Integer
Dim Frm_SortRange01 As Range

GeoCodeTableStartRow = 2

Set Frm_SortRange01 = Range("2:48") ' <===== Is this correct?

ThisWorkbook.Activate
Sheets("GeoCodeTable").Activate
Sheets("GeoCodeTable").Select

' I got this code from recording a Macro in Excel Changed code to automate Original Code
' Changed Code ---------------------------- Original Code

Rows(Frm_SortRange01).Select ' <----------- Rows("2:48").Select
Application.CutCopyMode = False
Selection.Sort Key1:= _
Range("A" & GeoCodeTableStartRow), _ ' <---- Range("A2"),
Order1:=xlAscending, Key2:= _
Range("C" & GeoCodeTableStartRow) _ ' <----- Range("C48"),
, Order2:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
<input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden">

 

[JoeMo]

Change Range to Rows:

Set Frm_SortRange01 = Rows("2:48")

 

[AlphaFrog]

This is correct...

Set Frm_SortRange01 = Range("2:48")

This is not...

Rows(Frm_SortRange01).Select

Change it to just this...

Frm_SortRange01.Select

Frm_SortRange01 is a range object.

 

[FJSeminario]

Change Range to Rows:

Set Frm_SortRange01 = Rows("2:48")

JoeMo, What I really want to replace are the values
of 2 and 48 with variables
<input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden">

 

[FJSeminario]

This is correct...

Set Frm_SortRange01 = Range("2:48")

This is not...

Rows(Frm_SortRange01).Select

Change it to just this...

Frm_SortRange01.Select

Frm_SortRange01 is a range object.

AlphaFrog. Thanks for your response

By the way I am running Excel 2003
on an XPProfessional Operating System

What I really want to know is how to replace the
Range values of 2 and 48 with variables

I have changed the name of the object to reflect it
from Frm_sortTange01 to Rng_SortRange01
and the new code looks like this

Dim Rgn_SortRange01 As Range

ThisWorkbook.Activate
Sheets("GeoCodeTable").Activate
Sheets("GeoCodeTable").Select

Set Rgn_SortRange01 = Sheets("GeoCodeTable").Range("2:48")
Rgn_SortRange01.Select
Selection.Sort Key1:=Range("A2"), _
Order1:=xlAscending, Key2:=Range("C2"), _
Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

The selection now works well but it stops at
SelectionSortKwy1 with the following error

Run-time error '1004':

The sort reference is not valid. Make sure that it's
within the data you want to sort, and the first sort
By box isn't the same or blank
<input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden">

 

[AlphaFrog]

It would be best if you surround your VBA code with code tags e.g.; [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier. When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign # (a.k.a. hash, hex, octothorp)

The syntax of your code looks correct. ThisWorkbook is the workbook with the VBA code. Make sure you haven't mistaken it for some other open workbook, and that it has data in cells A2 and C2 on the GeoCodeTable worksheet.

The code below is an example of how you can use variables for the Range of rows. It doesn't "fix" anything (intentionally) with your previous code though.

    Dim Rgn_SortRange01 As Range
    Dim RowStart As Long, RowEnd As Long
    
    RowStart = 2
    RowEnd = 48
    
    With ThisWorkbook.Sheets("GeoCodeTable")
    
        Set Rgn_SortRange01 = .Range(RowStart & ":" & RowEnd)
        
        Rgn_SortRange01.Sort Key1:=.Range("A" & RowStart), _
                             Order1:=xlAscending, Key2:=.Range("C" & RowStart), _
                             Order2:=xlAscending, Header:=xlGuess, _
                             OrderCustom:=1, MatchCase:=False, _
                             Orientation:=xlTopToBottom, _
                             DataOption1:=xlSortNormal, _
                             DataOption2:=xlSortNormal
                             
    End With

 

[FJSeminario]

It would be best if you surround your VBA code with code tags e.g.; [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier. When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign # (a.k.a. hash, hex, octothorp)

The syntax of your code looks correct. ThisWorkbook is the workbook with the VBA code. Make sure you haven't mistaken it for some other open workbook, and that it has data in cells A2 and C2 on the GeoCodeTable worksheet.

The code below is an example of how you can use variables for the Range of rows. It doesn't "fix" anything (intentionally) with your previous code though.

    Dim Rgn_SortRange01 As Range
    Dim RowStart As Long, RowEnd As Long
    
    RowStart = 2
    RowEnd = 48
    
    With ThisWorkbook.Sheets("GeoCodeTable")
    
        Set Rgn_SortRange01 = .Range(RowStart & ":" & RowEnd)
        
        Rgn_SortRange01.Sort Key1:=.Range("A" & RowStart), _
                             Order1:=xlAscending, Key2:=.Range("C" & RowStart), _
                             Order2:=xlAscending, Header:=xlGuess, _
                             OrderCustom:=1, MatchCase:=False, _
                             Orientation:=xlTopToBottom, _
                             DataOption1:=xlSortNormal, _
                             DataOption2:=xlSortNormal
                             
    End With

AlphaFrog, This code did the trick, thanks for your time, I am now able to execute the macro just fine, again Thank you very much
FJSeminario

<input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden">

 

[FJSeminario]

JoeMo & AlphaFrog

Thanks for your replies, I have used a combination of your suggestions and my macro works fine.

Consider this Thread satisfactorily RESOLVED

Thanks
FJSeminario
<input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden">