Delete rows in an Excel file

[Dr Doneau Probst]

I am trying to use the macro recorder, but when I select the first 6 rows of the spreadsheet and use the rows delete function it does not work when I play back the macro.

I have tried highlighting the rows and using the delete key, as well as right click and delete. When I play the macro back it does not select the rows and does not delete the rows. 

What am I missing? 

[rberq]

Are you sure that the macro is even running?

 

As a FIRST step, look at the macro with the Script Editor and insert a Text Box Display command at the beginning, displaying some simple message like "Macro is running".  Then when you play back the macro you should see the message pop up on the screen.  Once you confirm that it actually runs, we can look into what it is and isn't doing. 

[Dr Doneau Probst]

Yes, it is running.  I can left click and highlight rows while recording the macro, but when I play the macro back, the mouse moves down the rows but nothing highlights.

It delete the last row only. 

[Dr Doneau Probst]

Nevermind. The problem was in the downloaded file.  I wrote an excel file from scratch and it worked fine.  This was my first time using Macro Express 5. 

I should have known better. Sorry to have bothered the community. 

[rberq]

Open the macro with the Script Editor, copy it, and post it here.  (Use the option to Copy Command Text, not Copy.)

[rberq]

OK - glad you found the issue. 

 

[Cory]

General thoughts:

• I don't like the macro recorder becasue I would experience similar frustration in cases like this. I advise all newbs to get into directly writing their macros as soon as possible. I advise you look at what it has recorded but also think of how you do it and write the code yourself. 
• Avoid using the mouse.
• Try writing a test macro as simple as possible that only attempts to do one thing. Keep it as simple as possible and make it independent. Then post it and a sample spreadsheet if necessary here so we can look at it and experiment. 
• Check out F5 "Go To". 
• Ctrl + Space selects and entire column, Shift + Space selects an entire row. Delete will delete the contents. 
• Ctrl + Shift "+" inserts a row. Ctrl + [minus key] deletes the entire row. Click here.
• In general, explore all the keyboard shortcuts in Excel. 

 

[acantor]

Cory's Excel hotkeys are the same ones I use to automate spreadsheet tasks via Macro Express.

 

Think of the Macro Recorder as a tasty appetizer, but nutritionally empty. When I teach sessions on Macro Express, I mention the Recorder but the topic is never a course objective. Although it's possible to produce working macros viathe Recorder, the scripts are likely to be very fragile, especially when capturing mouse actions.

[Cory]

19 minutes ago, acantor said:

tasty appetizer

LOL. Well put. 

 

Also I would add that I find that copying all a worksheet or exporting it, then processing it in ASCII File Process and pasting it back in or importing is easier. But of course that's not always an option.