Macro to Retain 10 rows from Row 10 up to before text like Total

I have sheets from "Bad Debts Recovered" onwards

I need VBA Code to retain 10 rows on those sheets from this sheet up to last sheet where there is no data from row 10 in Col A up to the row before tect like Total is in Col A


I have tried to write code to do this before it does nothing

Kindly amend my code

Please Login or Register  to view this content.

Hello, please find below the code that I created, it works on any of those sheets and it starts from row 10:

Please Login or Register  to view this content.

Insert row(s) when less than 10?

Please Login or Register  to view this content.

If you have row(s) that contains "total" before row 10 and ignore them then
try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Hi Mhdb

Your code is only leaving one row after running the macro

I need to retain a minimum of 10 blank rows on all sheets where the sheets contain no data in Col A10 up to before text like "Total"

I have attached a workbook after running your code and another workbook of how it should look like after running the macro (What it should be)


Kindly amend your code accordingly

Thanks Jindon

It works 100% on those sheets where there is no data in Col A from row 10 up to one row before Total

Where any of the data has data in Col A10 onwards, these sheets must be left intact

Not really sure,
If this is not how you want, I need to see your workbook with before/after.

Please Login or Register  to view this content.

Thanks but not what I require


See Before and After Files

OK, see if this works,

Please Login or Register  to view this content.

Many thanks Jindon


This works 100%

I managed to amend your code see below and on checking it is working

Please Login or Register  to view this content.

If it is working as you want, that's nice.

Good on you.

Thanks Jindon. It is working 100%