Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
From Column to Row without blank cells
I have 3 columns of data 1st being names then date and time and finally event
scores, I want to move the names colunm to rows but only taking one instance, then leave the date and time in column format. Now I need to fill each row with the score relivent without having a blank cell. I have tried using IF statments but after the first cell has been used it moves over 1 cell and down 1 cell to put the next event score. Please can you help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
From Column to Row without blank cells
Sub movetorows()
Rows(1).Insert 'Insert blank row to simplifiy coding StartColumn = 5 'Start putting data into column E NewColumn = StartColumn LastRow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To LastRow If Range("A" & RowCount) < "" Then NewName = Range("A" & RowCount) Score = Range("C" & RowCount) Set c = Rows(1).Find(what:=NewName, _ LookIn:=xlValues) If Not c Is Nothing Then Set NewCell = c.End(xlDown).Offset(1, 0) NewCell.Value = Score Else Cells(1, NewColumn) = NewName Cells(2, NewColumn) = Score NewColumn = NewColumn + 1 End If End If Next RowCount End Sub "REVILO" wrote: I have 3 columns of data 1st being names then date and time and finally event scores, I want to move the names colunm to rows but only taking one instance, then leave the date and time in column format. Now I need to fill each row with the score relivent without having a blank cell. I have tried using IF statments but after the first cell has been used it moves over 1 cell and down 1 cell to put the next event score. Please can you help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
From Column to Row without blank cells
Thanks Joel, Im some way to achieving my aim, however after running this code
all that happens is cell E1 has Name cell E2 has Score and A1 and A2 loop through and enter final Name and Score in their recpective cells. I have tried to make fine adjustments but my experience with VBA is limited. If I may add further clarity, after running a pivot table on my data the table it produced is what im aiming to achieve although I would prefer to build my own data table. Thanks for yor efforts thus far have a great 2008. REVILO "Joel" wrote: Sub movetorows() Rows(1).Insert 'Insert blank row to simplifiy coding StartColumn = 5 'Start putting data into column E NewColumn = StartColumn LastRow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To LastRow If Range("A" & RowCount) < "" Then NewName = Range("A" & RowCount) Score = Range("C" & RowCount) Set c = Rows(1).Find(what:=NewName, _ LookIn:=xlValues) If Not c Is Nothing Then Set NewCell = c.End(xlDown).Offset(1, 0) NewCell.Value = Score Else Cells(1, NewColumn) = NewName Cells(2, NewColumn) = Score NewColumn = NewColumn + 1 End If End If Next RowCount End Sub "REVILO" wrote: I have 3 columns of data 1st being names then date and time and finally event scores, I want to move the names colunm to rows but only taking one instance, then leave the date and time in column format. Now I need to fill each row with the score relivent without having a blank cell. I have tried using IF statments but after the first cell has been used it moves over 1 cell and down 1 cell to put the next event score. Please can you help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
From Column to Row without blank cells
Here is the result of running my code with test data. I start with data in
Range A1:C11. the code inserts a row so the data moves to A2:C12. The results is placed in columns E and F. I don't quite understand the description of the problem you described in your last posting. Is there otrher data in your worksheet besides the data in columns A - C? Try place just column A-c data in a newworksheet and try re-running the code. A B C E F 1 Joel Gary 2 Joel 1/1/2007 123 123 10 3 Joel 1/1/2007 3 3 11 4 Joel 1/1/2007 4 4 12 5 Joel 1/1/2007 5 5 13 6 Joel 1/1/2007 6 6 14 7 Gary 1/1/2007 10 15 8 Gary 1/1/2007 11 9 Gary 1/1/2007 12 10 Gary 1/1/2007 13 11 Gary 1/1/2007 14 12 Gary 1/1/2007 15 "REVILO" wrote: Thanks Joel, Im some way to achieving my aim, however after running this code all that happens is cell E1 has Name cell E2 has Score and A1 and A2 loop through and enter final Name and Score in their recpective cells. I have tried to make fine adjustments but my experience with VBA is limited. If I may add further clarity, after running a pivot table on my data the table it produced is what im aiming to achieve although I would prefer to build my own data table. Thanks for yor efforts thus far have a great 2008. REVILO "Joel" wrote: Sub movetorows() Rows(1).Insert 'Insert blank row to simplifiy coding StartColumn = 5 'Start putting data into column E NewColumn = StartColumn LastRow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To LastRow If Range("A" & RowCount) < "" Then NewName = Range("A" & RowCount) Score = Range("C" & RowCount) Set c = Rows(1).Find(what:=NewName, _ LookIn:=xlValues) If Not c Is Nothing Then Set NewCell = c.End(xlDown).Offset(1, 0) NewCell.Value = Score Else Cells(1, NewColumn) = NewName Cells(2, NewColumn) = Score NewColumn = NewColumn + 1 End If End If Next RowCount End Sub "REVILO" wrote: I have 3 columns of data 1st being names then date and time and finally event scores, I want to move the names colunm to rows but only taking one instance, then leave the date and time in column format. Now I need to fill each row with the score relivent without having a blank cell. I have tried using IF statments but after the first cell has been used it moves over 1 cell and down 1 cell to put the next event score. Please can you help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I can't autofill cells in a column if blank cells in between | Excel Worksheet Functions | |||
Deleting blank cells in a column | Excel Programming | |||
removing blank cells from a column | Excel Discussion (Misc queries) | |||
HELP: Last row of the column containing blank cells between non-blank cells | Excel Worksheet Functions | |||
Skipping blank cells in a column... | Excel Programming |