selecting non-continuous cells in Excel (2003)

Topic

New Reply

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

Reply | Quote

Replies

You must create a string that looks like

A2,C2,D2,F2,Q2,R2

To do that, use

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

Note: it’s better not to select ranges. Instead of

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

you can use the shorter and more efficient

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

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.

Reply | Quote

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
‘=============================================================

Reply | Quote

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.

Reply | Quote