Can i create a macro to solve my problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.

Angie said:
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
Click to expand...
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
Click to expand...
paste to from there. Then i could just click in the first cell perform the macro and
quicken up this horrid task
 
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.

Angie said:
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.
Click to expand...
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
 
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
 
Can you tell us how *you* recognise which records go together? Is there
common data somewhere in the records?
 
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?
 
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?
 
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

Angie said:
The function below moved the cell from row 3 to be in the colums next to
Click to expand...
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
Click to expand...
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
 
Back
Top