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:

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
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,384
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,384
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">
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

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