How to add a signature at the bottom of list?

[mso3]

Hi friends,
The following macro is working fine with correct output but I want to insert a signature of a class teacher at the bottom automatically. The range is from columns ‘m-w’ and the passout students varies time to time as per different divisions of that particular standard. I want to skip two rows after the last entry of passout student and then insert a signature of class teacher in column ‘V’.

Suppose the last entry of passout student is on row 56 then I want to skip row 57 and 58; insert a designation ‘Class Teacher’ in cell ‘V59’ and the name of the teacher ‘John Turco’ in cell ‘V60’. Finally I want to add a border to this table from range ‘M1-W61’; this bottom border changes time to time as per the entries of passout students.

Note that the row 5 is blank and hidden.

Please Login or Register  to view this content.

Any help will be appreciated.

Thanking you,

[JBeaucaire]

Without knowing where you're going to get the teacher's name from, this code at the end would insert the values as you've described:

Please Login or Register  to view this content.

[mso3]

Hi JBeaucaire,
Excellent! It's working absolutely fine as per my requirement. However a little revision is require. Sorry for not mentioning from where I'm getting the name of class teacher. I'm getting the name of class teacher from Information sheet cell address '=Information!$r$10'.

Please do this little amendment to solve the problem.

Thank you very much.

[JBeaucaire]

Please Login or Register  to view this content.

[mso3]

Hi JBeaucaire,
It's working fine but a another problem is there. Next time the signature repeats each time because we are not deleting the first region so the code inserts a next time signature at the bottom of it which I don't want. Secondly I want to skip 2 rows (leave blank rows) in between the last entry and the signature as mentioned before.

If the last entry is on row 56 then the signature should be on row 59 and 60 respectively.

row 59: Class teacher
row 60: =Information!$r$10 'The name of teacher

Solution please!

Thank you,

[JBeaucaire]

I am guessing at what you need changed regarding running your macro a second time. The macro appears to do a ".currentregion.clearcontents" at the beginning in an attempt to "reset the area". Is that not working for you? Perhaps this would do a better job:

Please Login or Register  to view this content.

Hopefully that will clear enough that inserting the signature later works normally and correctly.



Make note of how we are using OFFSET() and you can edit as needed:

Please Login or Register  to view this content.

[mso3]

Ok! It now solved the repeating signature problem for this time because the entries of pass students varies from standard to standard. So the clear content should not be restricted to row 11 it should be at the end of region from m6. The border starts from m1 so it should be deleted before running the macro again to avoide several borders at the bottom as many times I run the macro a border is added.

Suggest me a code to delete the border and signature first and run the macro to get perfect output.

I understood the offset function and changed the number 2 to 3 to get 2 blank rows in between entries and the signature.

Thank you for guidance.

Sorry for trouble.

Thanking you.

[JBeaucaire]

Try switching from .CLEARCONTENTS to .CLEAR

NOTE: The "11" in the Resize is in the columns parameter. We are starting at M6, the resizing down the same number of rows as are currently used, then resizing to 11 columns to get all the way out to column W.

[mso3]

Hi JBeaucaire,
Excellent! Now it's working absolutely fine as per my requirement.

I appreciate you for your kind help and guidance to improve my knowledge.

Thank you and have a nice day.

[JBeaucaire]

Glad to help. Cya next time.