Needing a special Macro really really badly

[Daniel Cremin]

Hi please please please help me if you can, i will be so grateful. I have a marks and grades system where each time a new student is added using a pre-excel 97 dialog form a special "master-row" called "specialrow" gets copied (in contains all the formulaes) and then inserted on top of the special row - the data entered on the form is then pasted into cells on this new row. The system is designed to cater for up to 30-32 students a year but it can be less if that is the case - i need a macro that at the end of the year can "clean down" the last years system so to speak by deleting all the inserted student rows so that the "special row" row of cells is just below the row header (row 2) on the worksheet called processing and storage - please note that there is also cells below the special row and that the first two rows of the workbook are Titles/ Headers that cant be deleted. Is there anyway to "reset" the system for new students to be entered in the next academic year from scratch? Im sure theres a way to delete all rows between the second row of a worksheet and the "special row" which could be any number of rows (but never more than 32 at max down from the header).

Thanks so much in advance - im having panic attacks over this so any help would be greatly appreciated.

[Abcd]

A 12 line question, with only 4 sentences and no punctuation except full stops, is difficult to read.

[nisht]

Download My programme Workbook "Result Sheet"

It is in my download section. File nos is 9

Result Sheet is programme where you can add nos of students and get the print of marksheets with one go or as you select. I think you will like this. I had programme this for one of my friend.

http://www.pexcel.com/download.htm

nishith desai

[Daniel Cremin]

Does ne one know how to delete all rows so that a named range row is directly beneath the second row on the worksheet (the column headers/ titles bit). I have a number of students and need to be able to "clear down" the system of all of them at the end of the year and simply have the "special row" that all these "records" are based on appear, so that next years data can be added. it has to be an automatic measure, through VBA.

[Daniel Cremin]

thanks nisht for your file, only problem is that it really didnt help me with this particular problem - thanks very much anyway for taking the time, and your site is great.

[Daniel Cremin]

hang on would this do it? if i had the macro that adds in a new row also have a bit of code that adds a 1 to every new copied row and then used a for next statement to search down the maximum range list of students in the column where it is stored and delete all matching cells!!!

[NateO]

Daniel with Respect to:

Does ne one know how to delete all rows so that a named range row is directly beneath the second row on the worksheet (the column headers/ titles bit).

The answer is no. Notice below:

Sub myRw()
Dim n As Integer, rw As Range
Set rw = Range("myrow")
n = rw.Offset(-1).Row
Worksheets(rw.Worksheet.Name).Range("3:" & n).Delete
End Sub

Change the myrow to the name of your extra special row & voila...

Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: nateo on 2002-03-19 10:18 ]