I need help with an excel macro that will copy the contents of a range
to a single column of rows.

Scenario:
user selects continuous range R
user executes the macro
user is prompted to select a cell C
If C is included in R user is prompted to select a non-inclusive cell
Data from R is copied, left-to-right, top-to-bottom to destination
cells starting at C

Example. Assuming one char/cell

a b
c d

is copied as

a <- selected cell C
b
c
d

could you please help? TIA

Maybe this will work for you:

Option Explicit
Sub testme02()

Dim fRng As Range
Dim tRng As Range
Dim myRow As Range
Dim myCell As Range
Dim Resp As Long

Set fRng = Nothing
On Error Resume Next
Set fRng = Application.InputBox(Prompt:="Select a single area range", _
Type:=8).Areas(1)
On Error GoTo 0

If fRng Is Nothing Then
Exit Sub 'user hit cancel
End If

Set tRng = Nothing
On Error Resume Next
Set tRng = Application.InputBox(Prompt:="Select a single cell", _
Type:=8).Cells(1)
On Error GoTo 0

If tRng Is Nothing Then
Exit Sub
End If

'check if same workbook
If fRng.Parent.Parent.Name = tRng.Parent.Parent.Name Then
'check if same worksheet
If fRng.Parent.Name = tRng.Parent.Name Then
'check for overlapping
If Intersect(fRng, tRng.Resize(fRng.Cells.Count)) Is Nothing Then
'no overlap
Else
MsgBox "Please select non-overlapping ranges!"
Exit Sub
End If
End If
End If


If Application.CountA(tRng.Resize(fRng.Cells.Count)) 0 Then
Resp = MsgBox(Prompt:="Wanna overwrite existing data?", _
Buttons:=vbYesNo)
If Resp = vbNo Then
Exit Sub
End If
End If

For Each myRow In fRng.Rows
For Each myCell In myRow.Cells
tRng.Value = myCell.Value
Set tRng = tRng.Offset(1, 0)
Next myCell
Next myRow

End Sub


wrote:

I need help with an excel macro that will copy the contents of a range
to a single column of rows.

Scenario:
user selects continuous range R
user executes the macro
user is prompted to select a cell C
If C is included in R user is prompted to select a non-inclusive cell
Data from R is copied, left-to-right, top-to-bottom to destination
cells starting at C

Example. Assuming one char/cell

a b
c d

is copied as

a <- selected cell C
b
c
d

could you please help? TIA

--

Dave Peterson

Dave Peterson wrote:
Maybe this will work for you:

Sub testme02()
[snip]

Yes it does work perfect. Thank you very much for your time and reply.