Non contiguous to contiguous
 

Hi

My data is in non-contiguous cells within a named range "Data" (C8:AG18)

Could someone please assist with a macro that will copy the data from al
cells containing data within this range and place them contiguously in a column for charting purposes

Thank U

Non contiguous to contiguous
 

Neal,

Try this -

Sub PlaceData()

Dim rng As Range
Dim c As Range
Dim rngPlace As Range

'set this to be the starting point for your data
Set rngPlace = ActiveSheet.Range("G1")

For Each rng In ActiveWorkbook.Names("Data").RefersToRange.Areas
For Each c In rng
rngPlace.Value = c.Value
Set rngPlace = rngPlace.Offset(1, 0)
Next c
Next rng

End Sub

--
HTH,
Dianne Butterworth

In ,
Neal typed:
Hi,

My data is in non-contiguous cells within a named range "Data"
(C8:AG18).

Could someone please assist with a macro that will copy the data from
all
cells containing data within this range and place them contiguously
in a column for charting purposes.


Thank U

Non contiguous to contiguous
 

Thanks Dianne

One problem....cells without data in them are also sent to the mergeplace, therefore i get a lot of blank cells in the column
Can we first check to make sure the cell to be merged does contain data

Regard
Nea

----- Dianne Butterworth wrote: ----

Neal

Try this

Sub PlaceData(

Dim rng As Rang
Dim c As Rang
Dim rngPlace As Rang

'set this to be the starting point for your dat
Set rngPlace = ActiveSheet.Range("G1"

For Each rng In ActiveWorkbook.Names("Data").RefersToRange.Area
For Each c In rn
rngPlace.Value = c.Valu
Set rngPlace = rngPlace.Offset(1, 0
Next
Next rn

End Su

--
HTH
Dianne Butterwort

In
Neal typed
Hi
My data is in non-contiguous cells within a named range "Data
(C8:AG18)
Could someone please assist with a macro that will copy the data fro
al
cells containing data within this range and place them contiguousl
in a column for charting purposes
Thank

Non contiguous to contiguous
 

Sure...

Sub PlaceData()

Dim rng As Range
Dim c As Range
Dim rngPlace As Range

'set this to be the starting point for your data
Set rngPlace = ActiveSheet.Range("G1")

For Each rng In ActiveWorkbook.Names("Data").RefersToRange.Areas
For Each c In rng
If c.Value < "" Then
rngPlace.Value = c.Value
Set rngPlace = rngPlace.Offset(1, 0)
End If
Next c
Next rng

End Sub

--
HTH,
Dianne Butterworth

In ,
Neal typed:
Thanks Dianne,

One problem....cells without data in them are also sent to the
mergeplace, therefore i get a lot of blank cells in the column. Can
we first check to make sure the cell to be merged does contain data?

Regards
Neal

----- Dianne Butterworth wrote: -----

Neal,

Try this -

Sub PlaceData()

Dim rng As Range
Dim c As Range
Dim rngPlace As Range

'set this to be the starting point for your data
Set rngPlace = ActiveSheet.Range("G1")

For Each rng In
ActiveWorkbook.Names("Data").RefersToRange.Areas For
Each c In rng rngPlace.Value = c.Value
Set rngPlace = rngPlace.Offset(1, 0)
Next c
Next rng

End Sub

--
HTH,
Dianne Butterworth

In ,
Neal typed:
Hi,
My data is in non-contiguous cells within a named range "Data"
(C8:AG18).
Could someone please assist with a macro that will copy the
data from all
cells containing data within this range and place them
contiguously in a column for charting purposes.
Thank U