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

FJSeminario

New Member
Joined
Mar 26, 2011
Messages
12
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">
 
Last edited:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,890
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Change Range to Rows:
Rich (BB code):
Set Frm_SortRange01 = Rows("2:48")
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
This is correct...
Code:
Set Frm_SortRange01 = Range("2:48")


This is not...
Code:
Rows(Frm_SortRange01).Select

Change it to just this...
Code:
Frm_SortRange01.Select

Frm_SortRange01 is a range object.
 

FJSeminario

New Member
Joined
Mar 26, 2011
Messages
12
Change Range to Rows:
Rich (BB code):
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

New Member
Joined
Mar 26, 2011
Messages
12

ADVERTISEMENT

This is correct...
Code:
Set Frm_SortRange01 = Range("2:48")
This is not...
Code:
Rows(Frm_SortRange01).Select
Change it to just this...
Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
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.

Code:
    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

New Member
Joined
Mar 26, 2011
Messages
12
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.

Code:
    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

New Member
Joined
Mar 26, 2011
Messages
12
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">
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,711
Messages
5,833,251
Members
430,200
Latest member
ADLHMA2022

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
Top