moving rows

[Guest]

I have a large report in tab or csv format that creates 3 rows of data in
Excel that I want to move to a single row. Cut and paste will take forever.
What can I do?

 

[Frank Kabel]

Hi
what delimiter is used in the *.csv file and what is set in your Windows
registry?

Some workarounds:
- use 'Data - Text to columns' to separate the data
- rename the *.csv file to *.txt as then the textimport wizard is started
and you can specify the delimiter

 

[Gord Dibben]

How many columns is your original data?

If one column and your data is consistently 3 rows, this macro will work.

Sub ColtoRows_NoError()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Application.ScreenUpdating = False
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Application.ScreenUpdating = True
End Sub

If more than one original column, post back with more details.

If unfamiliar with macros see David McRitchie's "getting started" site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm


Gord Dibben Excel MVP

 

[Paulw2k]

Hi,

Assuming all data downloaded into column A then this macro should do it.

Sub ReorderMacro()
Rem This macro assumes all data is in Column A
Rem and moves the values in every 2nd and 3rd row
Rem into the adjacent columns in every 1st row.
Rem Using the autofilter removes the resulting empty rows.

Dim Rng As Range
Dim lOffset As Long

Set Rng = Range("A1")
With Rng
Do Until IsEmpty(.Offset(lOffset, 0))
Rem place values in next 2 cells down in columns immediately to
the right
.Offset(lOffset, 1).Value = .Offset(lOffset + 1, 0).Value
.Offset(lOffset, 2).Value = .Offset(lOffset + 2, 0).Value
Rem clear these values from original cells
.Offset(lOffset + 1, 0).Clear
.Offset(lOffset + 2, 0).Clear
Rem Move to next "1st" row (1,4,7,10, ... )
lOffset = lOffset + 3
Loop
End With

Rem delete empty Rows created
Set Rng = ActiveSheet.UsedRange

ActiveSheet.AutoFilterMode = False
With Rng
Rem Filter on first column for empty cells
.AutoFilter Field:=1, Criteria1:="="
Rem If More than one row is visible, remove all visible except row
1.
If .SpecialCells(xlCellTypeVisible).Count > .Columns.Count Then _
.Rows("2:" &
CStr(.Rows.Count)).Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False

End Sub


Hope this helps

Regards

Paul







End Sub