Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    selecting non-continuous cells in Excel (2003)

    This line of code
    ActiveSheet.Range("A" & i, "C" & i, "D" & i, "F" & i, "Q" & i, "R" & i).Select
    in the following block generates an error. Any ideas why would be helpful.
    Thanks,

    Here is the code in question:
    for i = 2 to RealLastRow '(a number indicating the last row of data)
    'back to the next cell to evaluate
    Range("L" & i ).Select

    If strDisp <> "9999" Then

    'THIS FOLLOWING LINE OF CODE GENERATES AN ERROR...

    ActiveSheet.Range("A" & i, "C" & i, "D" & i, "F" & i, "Q" & i, "R" & i).Select

    'THE ERROR FROM THE ABOVE LINE OF CODE IS: "Wrong number of arguments or invalid property assignment"

    Selection.Copy

    'paste the selected cells into the latest inventory worksheet...
    Sheets("Latest Inventory").Select


    'find the last row in the latest inventory sheet
    Call GetRealLastCell (THIS IS A SUB PROCEDURE THAT POPULATES GLOBAL VARIABLES WITH THE LAST ROW OF DATA AND LAST COLUMN OF DATA)

    Range("A" & RealLastRow).Select
    ActiveSheet.Paste
    Sheets("Original Dataset").Select
    Application.CutCopyMode = False
    End If
    Next i

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: selecting non-continuous cells in Excel (2003)

    You must create a string that looks like
    <code>
    A2,C2,D2,F2,Q2,R2
    </code>
    To do that, use
    <code>
    ActiveSheet.Range("A" & i & ",C" & i & ",D" & i & ",F" & i & ",Q" & i & ",R" & i).Select
    </code>
    Note: it's better not to select ranges. Instead of
    <code>
    ActiveSheet.Range("A" & i & ",C" & i & ",D" & i & ",F" & i & ",Q" & i & ",R" & i).Select
    Selection.Copy
    </code>
    you can use the shorter and more efficient
    <code>
    ActiveSheet.Range("A" & i & ",C" & i & ",D" & i & ",F" & i & ",Q" & i & ",R" & i).Copy
    </code>
    It's even possible to perform the copy and paste operation in one instruction, but I don't know enough about GetRealLastCell to tell how that should be incorporated.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: selecting non-continuous cells in Excel (2003)

    Thanks again Hans. Works just fine.

    FYI, the GetRealLastCell procedure is a handy piece of code that we use very frequently in most of our Excel apps.
    I don't know who gets credit for writing it. I may have found it here in this forum.
    '================================================= ============
    Option Explicit
    Public RealLastRow As Long
    Public RealLastColumn As Long

    Public Sub GetRealLastCell()

    Range("A1").Select

    On Error Resume Next

    RealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
    RealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
    Cells(RealLastRow, RealLastColumn).Select
    End Sub
    '================================================= ============

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: selecting non-continuous cells in Excel (2003)

    It's quite possible that you found the GetRealLastCell function here, but I don't think it was written by a Lounger. The code has been around for quite a while and it is available from a lot of websites. I think I first saw it here: Beyond Technology :: Microsoft Excel - Identifying the Real Last Cell.

Posting Permissions

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