Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

syntax of Range() 2

Status
Not open for further replies.

mscallisto

Technical User
Jun 14, 2001
2,990
US
I'm trying to position to the last used row in a sheet.

In the following code I can never get the correct syntax of this line:
Range("strTheEnd & Str(intLastRow)").Select

Code:
Sub colorval()
Dim strTheEnd As String
Dim intLastRow As Integer
intLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
strTheEnd = "A"
For Ii = 2 To intLastRow  ' Skip Header Row
' Rows(Ii).Columns(12) = Rows(Ii).Columns(1).Font.ColorIndex
  Rows(Ii).Columns(12) = Rows(Ii).Columns(1).Interior.ColorIndex
  
' I want (Blue ColorIndex 5)to sort before (Red ColorIndex 3)
  If Rows(Ii).Columns(12) = 3 Then
    Rows(Ii).Columns(12) = 6
  End If
Next Ii
Cells.Select
Selection.Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=xlYes, _
   OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
   DataOption1:=xlSortNormal

'example of what I'm trying to do 
'Range("A & Ii").Select

Range("strTheEnd & Str(intLastRow)").Select
End Sub
 
Code:
Range(strTheEnd & intLastRow).Select


Have fun.

---- Andy
 



hi,
Code:
Sub colorval()
    Dim strTheEnd As String
    Dim intLastRow As Integer
    intLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    strTheEnd = "A"
    For Ii = 2 To intLastRow  ' Skip Header Row
        With Cells(Ii, 12)
            .Value = .Interior.ColorIndex
        ' I want (Blue ColorIndex 5)to sort before (Red ColorIndex 3)
            If .Value = 3 Then .Value = 6
        End With
    Next Ii
    Cells.Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=xlYes, _
       OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
       DataOption1:=xlSortNormal
    
    'example of what I'm trying to do
    'Range("A & Ii").Select
    
    Cells(intLastRow, strTheEnd).Select
End Sub


Skip,

[glasses] [red][/red]
[tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top