Fixed Vs Dynamic Range and Delete Blank Rows Questions

[Excelnewb1567]

Hello.
Currently my input and output ranges are fixed so if I add even a single row, I would have to adjust all fixed ranges in VBA.
My first question: How can i change my fixed input and output ranges into dynamic ranges? (In other words if i add another row I would not have to manually change all other ranges)

Second question is: DeleteBlankRows has to be the last to run in RunAll macro in order to execute correctly.
Is there a way to change it, so it does not matter if it first or last to run and it would still delete all blank rows.

Code is below along with the file
Thank you.

(Notes: So I started learning VBA like a week ago, so if my questions seem silly it's cuz of that. Also, I would appreciate any feedback regarding how to improve the file to make it more dynamic or something. Thanks)

Please Login or Register  to view this content.

[Marc L]

Hi !

Just name your ranges in Excel for example like Grade9 and on VBA side [Grade9].Copy …

Or just with a blank row between grades, easier …

[Excelnewb1567]

Thank you for your response.
I am not sure I understood your response.

If define 9th grade as a custom formula Grade9 and then add it to Macro

old

Please Login or Register  to view this content.

new

Please Login or Register  to view this content.

The macro does not produce any result. Also the output range is still fixed.

I found some examples of how to change fixed to dynamic range, but they only seem to address the inputs side.