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?

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)

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>

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)
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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?
> >