Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells with Values Edit
I wish to place the following values into a single row:
148 407 118 10003 169 364 15 222 i.e. 148 407 118 10003 169 364 15 222 I need to repeat this task over and over. The configuration of the cells and values can be varied by the number of rows and cells they occupy, no standard pattern will exist. i.e the data could quite easily look like this: 186 452 12 12 212 50 50 268 47 47 10018 74 10084 58 58 285 141 141 399 143 I therefore need a sub in vba to count the number of rows and columns with data before using this to organise it into a single row. My first thought would be a select region then use the COUNTA function? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells with Values Edit
Public Sub ProcessData()
Dim iLastRow As Long Dim iLastCol As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 2 Step -1 iLastCol = Cells(i, "A").End(xlToRight).Column Cells(i, "A").Resize(, iLastCol).Copy _ Cells(i - 1, "A").End(xlToRight).Offset(0, 1) Next i Rows(2).Resize(iLastRow - 1).Delete End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I wish to place the following values into a single row: 148 407 118 10003 169 364 15 222 i.e. 148 407 118 10003 169 364 15 222 I need to repeat this task over and over. The configuration of the cells and values can be varied by the number of rows and cells they occupy, no standard pattern will exist. i.e the data could quite easily look like this: 186 452 12 12 212 50 50 268 47 47 10018 74 10084 58 58 285 141 141 399 143 I therefore need a sub in vba to count the number of rows and columns with data before using this to organise it into a single row. My first thought would be a select region then use the COUNTA function? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells with Values Edit
Thanks - That did the job
Simon Bob Phillips wrote: Public Sub ProcessData() Dim iLastRow As Long Dim iLastCol As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 2 Step -1 iLastCol = Cells(i, "A").End(xlToRight).Column Cells(i, "A").Resize(, iLastCol).Copy _ Cells(i - 1, "A").End(xlToRight).Offset(0, 1) Next i Rows(2).Resize(iLastRow - 1).Delete End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I wish to place the following values into a single row: 148 407 118 10003 169 364 15 222 i.e. 148 407 118 10003 169 364 15 222 I need to repeat this task over and over. The configuration of the cells and values can be varied by the number of rows and cells they occupy, no standard pattern will exist. i.e the data could quite easily look like this: 186 452 12 12 212 50 50 268 47 47 10018 74 10084 58 58 285 141 141 399 143 I therefore need a sub in vba to count the number of rows and columns with data before using this to organise it into a single row. My first thought would be a select region then use the COUNTA function? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup values in textbox and edit row | Excel Programming | |||
converting formulas into values using a macro in visual basic edit | Excel Programming | |||
Getting pre edit values | Excel Programming | |||
edit x-values in XY Scatter? | Charts and Charting in Excel | |||
Edit/Find/Options/Look In/Values | Excel Worksheet Functions |