Macro to move data

[mikemcbain]

I have 4000 rows of data to move and very little macro experience?
The data is in variable sized blocks like this...
Trial 5 3-y-o C&G 800m Margin 8L x 1/2Hd Time 49.71
Fin Horse No. Jockey Trainer
1 DANERICH 4 Rowan Waymouth (a) Robbie Laing
2 SILKY APPLE 9 Aaron Austin (a) David Chapman
3 DARK AMBITION 8 Greg Childs Mark Minervini
4 DANTAGE 6 Craig Williams Tony McEvoy
5 3 Jason Patton Mark Young
6 SEIZE THE CROWN 7 Ben Smith (a) Mick Price
Scratch MR MARJU 1 Peter Healey
Scratch BULLMORE 2 Graeme Rogerson
Scratch ROUGH NIGHT 5 Mathew Ellerton

...and I want to move each blockfrom the second row "Fin" to the last row that I have manually highlighted "Ellerton"
to the same row as the Time 49.71 and one column to the right of 49.71 ie.five columns right and one row up.
This is way beyond me as I am just learning to record Macros. I have manual cut and pasted about 500 rows in 2 hrs!

Appreciate any help

Taswegian[/code]

 

[Tazguy37]

Welcome to MrExcel Board!

Have you tried using the macro recorder to record this operation a couple times? Try it, and paste the results here. I'm having trouble figuring out what range you want copied to where, just from the explanation.

 

[mikemcbain]

Many thanks for your help...
Pasted below is the record of moving three different blocks of data.

Sub movedata()
'
' movedata Macro
' Macro recorded 14/11/2005 by Mike McBain
'
' Keyboard Shortcut: Ctrl+a
'
ActiveWindow.SmallScroll Down:=6
Range("A1380:E1390").Select
Range("E1390").Activate
Selection.Cut
Range("F1379").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=12
Range("A1392:E1402").Select
Range("E1402").Activate
Selection.Cut
Range("F1391").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=12
Range("A1404:E1414").Select
Range("E1414").Activate
Selection.Cut
Range("F1403").Select
ActiveSheet.Paste
End Sub

 

[Derek]

Hi there

If I understand you correctly, I would suggest this is easier done by formula and a custom filter.

Try this on a copy of your data.
Insert extra blank columns to the left of your data so that your data starts in F1
Put this formula in A2 and scroll it across to E2 and then down to the end of your data.
=IF(LEFT($F2,9)="Fin Horse",F1,"")

Convert those formulas to values (copy..paste special / values)

Now use a custom autofilter on column F to bring all rows that start with "Trial " together, select those rows and delete them (delete entire rows). ie Filter...Custom...begins with "Trial"

regards'
Derek

 

[mikemcbain]

Sorry Derek, we are on different planets here I think?
Fin and Horse are in different cells
I don't know what scrolling it across means
When I go special paste values the cells all go blank

I have these five columns of data which are variable rows deep
I want to move from the second row of each block down to the end of the block into the five columns exactly to the right of the first row which remains in position

Thanks for your interest

The Taswegian

 

[Tazguy37]

Do the groups have blank lines between them? It looks like you're just deleting blank rows until there's only one between each group.

 

[mikemcbain]

Yes Taz each group is separated by a blank row.

 

[Derek]

Hi again

I think I understand what you are trying to do. But rather than moving each block to the right, I am suggesting you add 5 extra columns to the left and move the first row of each block to the left. Then by removing the original top rows of each block it will align as you describe. This is based on the consistency than each block starts with "Trial" and the second row of each block start with "Fin".

Okay, if "Fin" is in a separate column then we need to change the formula.
Assuming your last row of data is row 5000 (increase if more)

After inserting 5 blank rows so your data starts in F1
Select A2:E5000
type this formula =IF(LEFT($F2,3)="Fin",F1,"")
Hold down CONTROL on your keyboard and press ENTER
That will place the formula in all cells.
This will copy the row that starts with "Trial" into columns A:E

Now select columns A:E
go to Edit / Copy
then Edit / Paste Special... then in the dropdown box select VALUES

Now you need to remove the original rows that start with "Trial"
Do this by custom filtering on row F (as explained before), and delete those entire rows.

Incidentally, to scroll a formula across means to select the cell with the formula, then move the curser to the bottom right corner of that cell until it changes to a thin black cross. Now hold down the left mouse button and drag right (or drag down, depending on which way you want to scroll the formula).

regards
Derek

 

[Tazguy37]

How about something like this?:

Sub delBlanks()
'Delete blank rows (if Column A is blank, the row is blank) until there is only one blank row
'between each group of entries

Dim iRow As Long

    On Error Resume Next
    For iRow = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If (IsEmpty(Cells(iRow, 1)) Or Cells(iRow, 1).Value = "") And (IsEmpty(Cells(iRow - 1, 1)) Or Cells(iRow - 1, 1).Value = "") Then Rows(iRow).EntireRow.Delete
    Next iRow

End Sub

• Press Alt-F11 to open the VBE.
  Press Control-R to open the Project Explorer. (May be open already)
  Click "Microsoft Excel Objects" for the file you're working on (should expand the list of the ThisWorkbook module and any sheet modules.)
  
  Select Insert, Module from the drop down menus.
  
  Put your code in the right-hand window.
  Press Alt-Q to close the VBE and return to Excel.

Hope that helps!

 

[mikemcbain]

Derek

Thanks for all that but blowed if I can get it to work - if you don't mind I will concentrate on trying to develop a macro to accomplish this task because I feel I am more likely to succeed and learn something in the process.

Many thanks for your time and effort.

Mike

Tazguy

Your suggestion worked like a charm to remove all blank rows except one between each block of data.

BTW on another task I would like to be able to remove all the blank rows and will try to adapt your macro to do that too!

Now I can record macros alright but I cannot change the recorded data which is cell specific so the data moves 5 columns to the right and one row up?

This would be an invaluable lesson for me if you could teach me that little trick?

With thanks

Mike.