Non contiguous to contiguous

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
Back
Top