Maximum number of cells selected at one time

[shipman01]

I'm writing what should be a very simple little macro that copies data from one worksheet to another. All the values being copied are in col W at various locations. There are 51 cells that need to be selected, all non-adjacent. I was hoping to be able to grab these values all at one time, copy them, go to another sheet, paste.

However, I can't seem to get the cells selected. The line is simple: Range("W47,W95,W140,W185,W230,W275, ....) etc - 51 values in all

When I test this line, I get a runtime error:

Method range of object global failed

Is there a maximum number of non-adjacent cells that can be selected at one time? Or should I create a variable for these cells? Or is there a better way all together?

Thanks so much!!

Mary

 

[Jonmo1]

This seems very inefficent, and difficult to maintain..
What if the cells change for some reason in the future?

Is there any pattern to the cells you want copied?
Like if the cell in Column X = "copy me", then copy the cell in column W of the same row?
Anything like that?

 

[Rick Rothstein]

I believe the maximun string length for an argument to the Range function is 255 or there abouts. I agree with jonmo1... "This seems very inefficent, and difficult to maintain". Except for the first cell, all the other cells seem to be 45 rows apart... does that pattern continue for all your 51 cells? If so, we can give you a simple loop to replace your long Range command. Otherwise, you may have to set up an array of your cell addresses and iterate that.

 

[shipman01]

No, unfortunately there is no pattern. I have confirmed with the requester that the report being referenced will not change (but we all know how that goes).

This is a large report that is broken into sections with blank rows separating the sections. For each section, Col W has two cells at the top with text, then many empty cells, then a value at the end of the section.

I'm considering saving the report file with a temporary name, then deleting the rows with col W has a blank or non-numeric cell, and then taking the resulting rows of numbers as my range to copy. This would be an easy fix and (I think) cleaner. And in this way if a location does change, the macro will still work.

I could also take the cells one at a time, moving through Col W looking for numeric values, but that process of finding, copying, going to another sheet, pasting, moving back, and continuing would have to repeat 51 times, so that doesn't seem so efficient either. It would be nice if there were a way to select Col w and then copy all numeric cells ... and there may be a way, but I don't know it.

Do you think either of these approaches makes more sense? I'm open to suggestions.

Mayr

 

[Jonmo1]

No, unfortunately there is no pattern. I have confirmed with the requester that the report being referenced will not change (but we all know how that goes).

This is a large report that is broken into sections with blank rows separating the sections. For each section, Col W has two cells at the top with text, then many empty cells, then a value at the end of the section.

I'm considering saving the report file with a temporary name, then deleting the rows with col W has a blank or non-numeric cell, and then taking the resulting rows of numbers as my range to copy. This would be an easy fix and (I think) cleaner. And in this way if a location does change, the macro will still work.

I could also take the cells one at a time, moving through Col W looking for numeric values, but that process of finding, copying, going to another sheet, pasting, moving back, and continuing would have to repeat 51 times, so that doesn't seem so efficient either. It would be nice if there were a way to select Col w and then copy all numeric cells ... and there may be a way, but I don't know it.

Do you think either of these approaches makes more sense? I'm open to suggestions.

Mayr

There is..

Take a look at the specialcells method...

Something like

Range("W:W").SpecialCells(xlCellTypeConstants, 1).Copy

 

[shipman01]

PERFECT! Thank you.... I should have asked this question two hours ago!

Mary

 

[Rick Rothstein]

I'm considering saving the report file with a temporary name, then deleting the rows with col W has a blank or non-numeric cell, and then taking the resulting rows of numbers as my range to copy.

If all you want to do is copy the rows where the cell in Column W contains a number, you can do that with one line of code (provided there will not be more than 8192 such numeric rows total). Let's say you wanted to copy them to Sheet2, then this line of code should do that for you...

Columns("W").SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Copy Worksheets("Sheet2").Range("A1")

 

[shipman01]

Thanks....that would be handy. Unfortunately, the paste location varies depending on the date, as this routine will run daily until a month's worth of data is accumulated.

The SpecialCells method works like a charm, though. This was the only piece that was giving me an issue.

Thanks again!

Mary

 

[Rick Rothstein]

Thanks....that would be handy. Unfortunately, the paste location varies depending on the date, as this routine will run daily until a month's worth of data is accumulated.

The SpecialCells method works like a charm, though. This was the only piece that was giving me an issue.

I'm not clear... do you still have a problem or do you see the way around the issue your raise in the first paragraph? I would need to know more about your structure, but there is nothing magical about the Range("A1") I used... you can do the copy to any row you want. Let's say you wanted to put the new data right after any existing data on Sheet2, then something like this should work...

LastUsedRow = Worksheets("Sheet2").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
Columns("W").SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Copy Worksheets("Sheet2").Cells(LastUsedRow + 1, 1)

 

[shipman01]

Thank you .... I've got it now. Macro is finished and works beautifully. Thanks to each of you for all the help.

Mary