Macro for Copying Non-contiguous Range

[plant007]

Hello

I know it is possible to write a macro to copy individual cells to a new master worksheet but is it possible to create a macro to copy a number of non-contiguous ranges in multiple worksheets?

Thanks
Andy

 

[p45cal]

Yes, non-contiguous ranges have a number of Areas which can be iterated through.

 

[plant007]

Thanks for the speedy reply and very happy to hear - I will look up AREAS on the web but would you have any links etc that may be of assistance
Thanks
Andy

 

[Scott Huish]

From the help file:
Excel Developer Reference
Areas Collection
A collection of the areas, or contiguous blocks of cells, within a selection.
Remarks
There’s no singular Area object; individual members of the Areas collection are Range objects. The Areas collection contains one Range object for each discrete, contiguous range of cells within the selection. If the selection contains only one area, the Areas collection contains a single Range object that corresponds to that selection.
Example
Use the Areas property to return the Areas collection. The following example clears the current selection if it contains more than one area.
Visual Basic for Applications
If Selection.Areas.Count <> 1 Then Selection.Clear
Use Areas(index), where index is the area index number, to return a single Range object from the collection. The index numbers correspond to the order in which the areas were selected. The following example clears the first area in the current selection if the selection contains more than one area.
Visual Basic for Applications

If Selection.Areas.Count <> 1 Then
    Selection.Areas(1).Clear
End If

Some operations cannot be performed on more than one area in a selection at the same time; you must loop through the individual areas in the selection and perform the operations on each area separately. The following example performs the operation named "myOperation" on the selected range if the selection contains only one area; if the selection contains multiple areas, the example performs myOperation on each individual area in the selection.
Visual Basic for Applications

Set rangeToUse = Selection
If rangeToUse.Areas.Count = 1 Then
    myOperation rangeToUse
Else
    For Each singleArea in rangeToUse.Areas
        myOperation singleArea
    Next
End If

 

[plant007]

Thanks for this HotPepper and just going through it now. VBA all very new to me so may take me a while! I have just found out that '<>' means 'not equal to' and already know that '=' is not 'equals' but an 'assignment operator'. I also know the mathermatical symbols +-/* etc but what does "" mean? A non specified range etc?

Also I am noticing the following in macros;
With application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.Screenupdatng = False
or
if RDB_Last(1, .cells) < .range(StartCell).Row Then

I am unsure why the full stops and spaces are there?
Any help always appreciated
Thanks
Andy

 

[p45cal]

You need a text to follow; John Walkenbach's books called:
Microdoft Excel 20nn Power Programming with VBA
where you can replace nn for one of 00, 03, 07 or 10
is quite readable and let's you get hands on with code quite early without bags of preliminaries

 

[plant007]

Thanks for this - I have, and workin through, his Excel VBA for Dummies but this book looks like a step up - thanks for the tip
Andy

 

[Scott Huish]

"" is an empty string, in other words, it is blank. A text value with nothing in it.
When you combine comparison operators it means OR.

So:
<= means less than or equal to
>= means greater than or equal to
<> technically means less than or greater than, which if you think about this in turns of numbers, if it is greater than or less than the number, it can't be equal to it, thus it is usually referred to as not equal to.

 

[plant007]

Thanks Hotpepper - the fog is slowly lifting!