Hello:
The following code was written by Geoff and I copied it to a module in my Excel 2000.
It works perfect.
I need help with modifing it to work with my scenerio.
I need the user to select the range in column "A" because the addresses are not consistant.
Most are 3 rows long without blank rows.
Others are 2 rows and still others are 4 rows.
I'm able to get the range selection with the input box function.
However, I don't know how to modify this to accept the selection and continue on the next row in sheet 2.
Sub TransposeAddresses()
Dim bRow As Long, sht As Worksheet, y As Long
Set sht = Sheets("Sheet2")
bRow = Range("A" & Rows.Count).End(xlUp).Row
y = 2
For I = 1 To bRow Step 3
For x = 0 To 2
sht.Cells(y, x + 1).Value = Cells(I + x, 1).Text
Next x
y = y + 1
Next I
End Sub
Thank you
z2Gary
The following code was written by Geoff and I copied it to a module in my Excel 2000.
It works perfect.
I need help with modifing it to work with my scenerio.
I need the user to select the range in column "A" because the addresses are not consistant.
Most are 3 rows long without blank rows.
Others are 2 rows and still others are 4 rows.
I'm able to get the range selection with the input box function.
However, I don't know how to modify this to accept the selection and continue on the next row in sheet 2.
Sub TransposeAddresses()
Dim bRow As Long, sht As Worksheet, y As Long
Set sht = Sheets("Sheet2")
bRow = Range("A" & Rows.Count).End(xlUp).Row
y = 2
For I = 1 To bRow Step 3
For x = 0 To 2
sht.Cells(y, x + 1).Value = Cells(I + x, 1).Text
Next x
y = y + 1
Next I
End Sub
Thank you
z2Gary