Reformatting macro which needs to insert rows after a variable number of records.

[PeterWB]

Hi Guys

I'm new to the Forum.

I have an Excel table of some 600+ records and have devised a macro (Excel_Macro_1) which reformats the table, sorts it on one column into three categories ‘blank’, ‘Air’ and ‘emag’ and adds three functions to count the number of records in each category (abridged version of table attached as Example_1).

I require the macro then to insert a block of 5 further blank rows to fall into the ‘blank’ category. I’ve tried inserting 5 rows as the last 5 rows in the ‘blank’ category by selecting the first 5 rows in the ‘Air’ category and, from the context menu, selecting ‘insert’. By doing so, the addresses of the inserted rows are hard coded into the macro which becomes a problem if the ‘blank’ category contains more records than when the macro was first/last run, resulting in the blank rows being spread between two locations in the table.

I need somehow to instruct the macro to insert the 5 rows by reference to ‘the first row which is not blank’ but achieving this is beyond my Excel knowledge.

For the purposes of this query, sensitive data have been removed from the attached Excel file. The macro code however reflects the original spreadsheet upon which it was based.

I would appreciate some help.

PeterWB

Excel_Macro_1.docxExample_1.xlsx

[Artik]

Since you have counted empty cells, you can specify from which row of the table data to insert empty rows (the result from cell L1 +1) and expand the range to five (Resize(5) ).
After recording the macro, it is a good idea to get rid of all the Selects from the code by modifying the code accordingly.

Please Login or Register  to view this content.

Artik

[jindon]

PeterWB,

try

Please Login or Register  to view this content.

[PeterWB]

Hi Artik

That works fine - thank you so much for your help. I'm sure I will be back in the future.

PeterWB

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.