Can i create a macro to solve my problem

[Angie]

I have a spreadsheet which has records pasted in which are contained on 3 different rows(under each other
I need to put all these rows which belong to the reocrd on one row and am presently cutting and pasting them
There are a lot of records in the spreadsheet and I want to make this very laborius task quicker

I would like to create a macro to do this but tried and it seemed to reference the cells i used to record the macro and didn't let me change these for subsequent attempts using the macro

Is there a way i can reference the cell I am in and then work out which cells to cut and paste to from there. Then i could just click in the first cell perform the macro and quicken up this horrid tas

Any help would be greatly appreciated

 

[Harald Staff]

Hi

Definitely possible and definitely macro work. But how can a macro recognize your "row 1",
"row 2" and "row 3" of data ? Blank row between ? Start on 3-multiple row numbers ? Border
? Selecting start, as you say, is an option, but pretty slow and may get boring after a
while.

Paste your recorded code here and I'm sure someone can modify it easily.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

I have a spreadsheet which has records pasted in which are contained on 3 different rows(under each other)
I need to put all these rows which belong to the reocrd on one row and am presently cutting and pasting them.
There are a lot of records in the spreadsheet and I want to make this very laborius task quicker.

I would like to create a macro to do this but tried and it seemed to reference the cells

i used to record the macro and didn't let me change these for subsequent attempts using
the macro.

Is there a way i can reference the cell I am in and then work out which cells to cut and

paste to from there. Then i could just click in the first cell perform the macro and
quicken up this horrid task

 

[Angie]

here is the code

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 29/12/2003 by Nutstretch
'
' Keyboard Shortcut: Ctrl+n
'
Range("A279").Select
Selection.Cut
Range("B278").Select
ActiveSheet.Paste
Range("A280").Select
Selection.Cut
Range("C278").Select
ActiveSheet.Paste
End Sub


----- Harald Staff wrote: -----

Hi

Definitely possible and definitely macro work. But how can a macro recognize your "row 1",
"row 2" and "row 3" of data ? Blank row between ? Start on 3-multiple row numbers ? Border
? Selecting start, as you say, is an option, but pretty slow and may get boring after a
while.

Paste your recorded code here and I'm sure someone can modify it easily.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

I have a spreadsheet which has records pasted in which are contained on 3 different rows(under each other)
I need to put all these rows which belong to the reocrd on one row and am presently cutting and pasting them.
There are a lot of records in the spreadsheet and I want to make this very laborius task quicker.

i used to record the macro and didn't let me change these for subsequent attempts using
the macro.paste to from there. Then i could just click in the first cell perform the macro and
quicken up this horrid task

 

[Harald Staff]

Hi

Stand in any "top cell" -the A cell in the resulting single row, in your
posted sample A278. Then run this:

Sub Macro3()
Selection(1).Offset(1, 0).Cut _
Selection(1).Offset(0, 1)
Selection(1).Offset(2, 0).Cut _
Selection(1).Offset(0, 2)
End Sub

 

[Roger Whitehead]

Can you tell us how *you* recognise which records go together? Is there
common data somewhere in the records?

 

[Angie]

The data has been cut and pasted from a web phone directory. it arrives like this below. The first line consists of 3 columns. The middle column is deleted as not needed(Details). The 2 rows beneath belong to the record. It is consistant with every record
Studio 13 Details 01509 263808
216 Park Rd
Loughborough, Leicestershire LE11 2HJ


----- Roger Whitehead wrote: -----

Can you tell us how *you* recognise which records go together? Is there
common data somewhere in the records?

 

[Angie]

The function below moved the cell from row 3 to be in the colums next to the originally selected cell and then i get a debug message about cannot change part or a merged cell.
----- Angie wrote: -----

The data has been cut and pasted from a web phone directory. it arrives like this below. The first line consists of 3 columns. The middle column is deleted as not needed(Details). The 2 rows beneath belong to the record. It is consistant with every record
Studio 13 Details 01509 263808
216 Park Rd
Loughborough, Leicestershire LE11 2HJ


----- Roger Whitehead wrote: -----

Can you tell us how *you* recognise which records go together? Is there
common data somewhere in the records?

 

[Harald Staff]

Where's the merged cell(s) ? Your original code would also err in those
cases. Un-merge them.

--
HTH. Best wishes Harald
Followup to newsgroup only please

The function below moved the cell from row 3 to be in the colums next to

the originally selected cell and then i get a debug message about cannot
change part or a merged cell.

----- Angie wrote: -----

The data has been cut and pasted from a web phone directory. it

arrives like this below. The first line consists of 3 columns. The middle
column is deleted as not needed(Details). The 2 rows beneath belong to the
record. It is consistant with every record