Home |
Search |
Today's Posts |
#1
|
|||
|
|||
moving rows
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? |
#2
|
|||
|
|||
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 -- Regards Frank Kabel Frankfurt, Germany "cdshon" schrieb im Newsbeitrag ... 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? |
#3
|
|||
|
|||
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 On Tue, 4 Jan 2005 12:11:10 -0800, cdshon wrote: 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? |
#4
|
|||
|
|||
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(xlCellTypeVi sible).EntireRow.Delete End With ActiveSheet.AutoFilterMode = False End Sub Hope this helps Regards Paul End Sub "cdshon" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving rows with Hyperlink doesn't move hyperlink address | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) | |||
Multiple rows of data on a single axis (charting) | Charts and Charting in Excel | |||
Unable to select rows in the repeat rows on top option | Excel Discussion (Misc queries) | |||
Unhide rows | Excel Discussion (Misc queries) |