Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2006
    Posts
    4

    Unanswered: Creating a variable range

    I'm trying to create a routine that will allow me to use a variable range but am not having much luck. Can anyone tell me what I might be misunderstanding here?
    <<<<<<<<<<<< = differing lines....

    strColLtr = ConvertColumnNumberToLetter(LastCol%)
    strRangeToCopy = """" & "A1:" & strColLtr & LastRow& & """"
    rngMyRng = strRangeToCopy
    Sht.Range(strRangeToCopy).Select <<<<<<<<<<<<<<<<<<<<<<
    Selection.Copy

    when I use this, it works?
    strColLtr = ConvertColumnNumberToLetter(LastCol%)
    strRangeToCopy = """" & "A1:" & strColLtr & LastRow& & """"
    rngMyRng = strRangeToCopy
    Sht.Range("A1:G41").Select <<<<<<<<<<<<<<<<<<<<<<<<
    Selection.Copy

  2. #2
    Join Date
    Mar 2006
    Posts
    163
    Where are you defining LastRow?

    By the way you don't actually need the column letter.

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I don't know if this helps, but you could try this, using the cells object

    Code:
    Sub Test()
    Dim LastCol As Long
    Dim LastRow As Long
    
    LastCol = 6
    LastRow = 22
    
    ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastCol)).Select
    Selection.Copy
    
    End Sub
    OR even this

    Code:
    Sub Test()
    Dim RangeAddress As String
    Dim LastCol As Long
    Dim LastRow As Long
    
    LastCol = 6
    LastRow = 22
    
    ActiveSheet.Range("A1", Cells(LastRow, LastCol)).Select
    Selection.Copy
    
    End Sub
    Does that help ?


    MTB

  4. #4
    Join Date
    Jun 2006
    Posts
    4
    Mike,
    Thanks - this worked fine. I had defined my LastRow & LastCol elsewhere and tried converting them to a string to use. It appears that I can not use the string to pass to a range definition? I keep getting hung up on that from the record macro process....
    Thanks again, Pam

  5. #5
    Join Date
    Mar 2006
    Posts
    163
    Pam

    You can use a string to specify the range, the problem with your code could be because the string you created actually contained unneeded quotes.

  6. #6
    Join Date
    Apr 2006
    Posts
    47
    With ActiveSheet.UsedRange
    MsgBox .Columns.Count
    MsgBox .Rows.Count
    End With

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •