Hi folks,
I would like to write or record a macro in excel that will first check and see how many rows are 'occupied' in Sheet1 of a workbook, and then, in Sheet2 of the same workbook, delete all rows under that and write '~End' in the first row under that. So for example, if 8 rows are occuppied in Sheet1, the macro would go to Sheet2, delete rows 9 through the end of the worksheet (which happens to always be 120), and write '~End' in the first cell of the 9th row.
I think I need to write a macro or something, but I have little experience writing in VB (I grew up writing C/C++). I was wondering if anyone knows if I can simply record a macro in excel to do what I need, or if I need to write one. And if I need to write one, does anyone know of a good online resource to help me out?
thanks,
Jay9333
PS
I'll give a more detailed description of my problem below in case anyone is interested or it is of help:
I have a workbook allows my users to format their data so that they can transfer it from excel to another data management program. They past their data into 'Sheet1' so that it looks like the following example (these examples of course involve much smaller amounts of data then reality):
Before my users open the work book, Sheet1 is blank, and Sheet2 (which basically serves as a template) looks like the following example:
Sheet2:
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
My users will then open up the workbook, and past the following data (as an exmample) into Sheet1:
Sheet1:
PatientIDs Unknowns A04100027
PatientIDs Unknowns A04100043
PatientIDs Unknowns A04100055
PatientIDs Unknowns A04100068
PatientIDs Unknowns A04100092
PatientIDs Unknowns A04100108
PatientIDs Unknowns A04100114
PatientIDs Unknowns A04100120
The formulas in the third column of Sheet2 will then read the data from Sheet1 and result in Sheet2 looking as follows:
Sheet2:
PatientIDs Unknowns A04100027 06
PatientIDs Unknowns A04100043 06
PatientIDs Unknowns A04100055 06
PatientIDs Unknowns A04100068 06
PatientIDs Unknowns A04100092 07
PatientIDs Unknowns A04100108 07
PatientIDs Unknowns A04100114 07
PatientIDs Unknowns A04100120 07
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
I have all of this working fine right now, with the formulas and all. But basically, the problem is that the users will be pasting in dynamic amounts of data. Sometimes they may past in 7 rows, sometimes 90. So just in case, I have 120 rows in my template.
The users then save Sheet2 as a tab-delimited text file, and put a '~End' after the last row of the data they put in (so in the above example, they'd erase all the lines after the 8th line and but a '~End' on the 9th line. This text file is then ready to be imported into the other data management program I mentioned above.
I would like to write or record a macro that they can run from Sheet2 (after they've pasted their data for the day into Sheet1) to put the '~End' in for them. That way they could just past data in Sheet 1, go to sheet 2 and run the macro, and then save sheet two as a text file. It would be even nicer if the macro saved Sheet2 as a tab-delimited text file too! But that would just be icing on the cake. I'm sure there must be good VB rescources that would tell my how to do this out there.
thanks again,
jay
I would like to write or record a macro in excel that will first check and see how many rows are 'occupied' in Sheet1 of a workbook, and then, in Sheet2 of the same workbook, delete all rows under that and write '~End' in the first row under that. So for example, if 8 rows are occuppied in Sheet1, the macro would go to Sheet2, delete rows 9 through the end of the worksheet (which happens to always be 120), and write '~End' in the first cell of the 9th row.
I think I need to write a macro or something, but I have little experience writing in VB (I grew up writing C/C++). I was wondering if anyone knows if I can simply record a macro in excel to do what I need, or if I need to write one. And if I need to write one, does anyone know of a good online resource to help me out?
thanks,
Jay9333
PS
I'll give a more detailed description of my problem below in case anyone is interested or it is of help:
I have a workbook allows my users to format their data so that they can transfer it from excel to another data management program. They past their data into 'Sheet1' so that it looks like the following example (these examples of course involve much smaller amounts of data then reality):
Before my users open the work book, Sheet1 is blank, and Sheet2 (which basically serves as a template) looks like the following example:
Sheet2:
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
My users will then open up the workbook, and past the following data (as an exmample) into Sheet1:
Sheet1:
PatientIDs Unknowns A04100027
PatientIDs Unknowns A04100043
PatientIDs Unknowns A04100055
PatientIDs Unknowns A04100068
PatientIDs Unknowns A04100092
PatientIDs Unknowns A04100108
PatientIDs Unknowns A04100114
PatientIDs Unknowns A04100120
The formulas in the third column of Sheet2 will then read the data from Sheet1 and result in Sheet2 looking as follows:
Sheet2:
PatientIDs Unknowns A04100027 06
PatientIDs Unknowns A04100043 06
PatientIDs Unknowns A04100055 06
PatientIDs Unknowns A04100068 06
PatientIDs Unknowns A04100092 07
PatientIDs Unknowns A04100108 07
PatientIDs Unknowns A04100114 07
PatientIDs Unknowns A04100120 07
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
PatientIDs Unknowns [formula]
I have all of this working fine right now, with the formulas and all. But basically, the problem is that the users will be pasting in dynamic amounts of data. Sometimes they may past in 7 rows, sometimes 90. So just in case, I have 120 rows in my template.
The users then save Sheet2 as a tab-delimited text file, and put a '~End' after the last row of the data they put in (so in the above example, they'd erase all the lines after the 8th line and but a '~End' on the 9th line. This text file is then ready to be imported into the other data management program I mentioned above.
I would like to write or record a macro that they can run from Sheet2 (after they've pasted their data for the day into Sheet1) to put the '~End' in for them. That way they could just past data in Sheet 1, go to sheet 2 and run the macro, and then save sheet two as a text file. It would be even nicer if the macro saved Sheet2 as a tab-delimited text file too! But that would just be icing on the cake. I'm sure there must be good VB rescources that would tell my how to do this out there.
thanks again,
jay