Macro to move data

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
107
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
I hate using so many .Selects, but it works.

Code:
    Selection.Cut
    Selection.Offset(5, -1).Select
    ActiveSheet.Paste

Much easier to copy -- only 1 line. HTH
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
107
Office Version
  1. 365
Platform
  1. Windows
Beautiful and I will adapt it to several tasks!
 

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
107
Office Version
  1. 365
Platform
  1. Windows
Tazguy

I am really struggling trying to learn this macro writing thingo!

For the life of me I haven't been able to get your previous macro (that so beautifully removes all but one blank row from between my data) to at a later stage remove the remaining blank rows.

I just keep getting these crazy Syntax error messages and when I go to help or revert to my VBA book end up getting confused, crabby and snarl at my wife.

She would appreciate it if you could talk me through a couple of very very simple macros so I might actually have some little idea of what I am doing?

If you can help her, can we start with a stand alone macro that removes all rows where the A column is blank?

If I can handle that then I would like to know how to stop my macro recorder from making absolute Cell refs?

Enough for now I can feel confusion coming again!

Mike the Taswegian
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
mikemcbain said:
If you can help her, can we start with a stand alone macro that removes all rows where the A column is blank?

Here's one way:

In a standard module:

Code:
ActiveSheet.Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

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


mikemcbain said:
If I can handle that then I would like to know how to stop my macro recorder from making absolute Cell refs?

  • Start the macro recorder.
    Go to View, Toolbars, and select the Stop Recording toolbar.
    Select that toolbar, and Customize it.
    Add the Relative Reference button; Add or Remove Buttons, Stop Recording, Relative Reference.

How's that?
 

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
107
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

TazGuy

When I run the delblank A rows macro I get a message saying
Compile Error: Invalid outside procedure and the following vba text highlights?
xlCellTypeBlanks

What does that mean and what should I try next?

The Taswegian
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
You have to give the code a name in a standard module. Something like:

Code:
Sub testStuff()
at the beginning, and a line that says:

Code:
End Sub
at the end so the code knows when to stop.
 

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
107
Office Version
  1. 365
Platform
  1. Windows
Oh, now it works just fine! Why am I surprised?

Thanks Tazguy, back to my practice.

Taswegian
 

Watch MrExcel Video

Forum statistics

Threads
1,123,518
Messages
5,602,131
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top