Macro to move data

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
152
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]
 
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
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Oh, now it works just fine! Why am I surprised?

Thanks Tazguy, back to my practice.

Taswegian
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top