can I record a macro to delete rows dynamically? 2

[jay9333]

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

 

[SkipVought]

jay,

You ought to use the SEARCH feature -- lots of answers to this question like thread707-739945

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[KenWright]

Something along these lines to get you started
perhaps

Sub DelRowsSh2()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

fn1 = ActiveWorkbook.Name
fn2 = Left(fn1, Len(fn1) - 4)
Set sht1 = Worksheets("Sheet1&quot
Set sht2 = Worksheets("Sheet2&quot
LastRw = sht1.Cells(Rows.Count, "A&quot.End(xlUp).Row
sht2.Activate

Set Rng = sht2.Range(Cells(LastRw + 1, "A&quot, Cells(Rows.Count, "A&quot)
Rng.EntireRow.Delete
Cells(LastRw + 1, 1).Value = "~End"

ActiveWorkbook.SaveAs Filename:= _
"C:\4test\" & fn2 & ".txt", FileFormat:=xlText, _
CreateBackup:=False

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[jay9333]

Wow Ken, thank you so much. That code does just what I needed. The "save as text" part returned an error, but that is fine. If I can't get that to work, I can just have my users save the file manually.

But the functionality to automatically create the end of the file like your code does is great to have! Thanks again. I'm new to VBA, but it looks to be very helpful when working with office. I need to try to get my hands on it some more...

jay

 

[jay9333]

Thanks for the link and advice, Skip. I'm new here, so I'll keep your advice in mind as I continue to use this site. The link you provided helped me to understand Ken's code a bit more, so thanks!

jay

 

[KenWright]

OK, minor tweak but should now work in 2000-2003

Sub DelRowsSh2()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

fn1 = ActiveWorkbook.Name
fn2 = Left(fn1, Len(fn1) - 4)
Set sht1 = Worksheets("Sheet1&quot
Set sht2 = Worksheets("Sheet2&quot
LastRw = sht1.Cells(Rows.Count, "A&quot.End(xlUp).Row
sht2.Activate

Set Rng = sht2.Range(Cells(LastRw + 1, "A&quot, Cells(Rows.Count, "A&quot)
Rng.EntireRow.Delete
Cells(LastRw + 1, 1).Value = "~End"

ActiveWorkbook.SaveAs Filename:= _
"C:\4 test\" & fn2 & ".txt", FileFormat:=xlText, _
CreateBackup:=False

Application.ScreenUpdating = True
MsgBox ("All Done - This file will now close&quot
Application.DisplayAlerts = True
ThisWorkbook.Saved = True
ActiveWorkbook.Close savechanges:=True

End Sub

Regards
Ken............



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[jay9333]

Yeah, I'm using Excel 2002, SP-2. Sorry for not mentioning that before. The error is still showing up, and the debugger takes me to:

ActiveWorkbook.SaveAs Filename:= _
    "C:\4 test\" & fn2 & ".txt", FileFormat:=xlText, _
    CreateBackup:=False

... but its no big deal. I'll play around with it, but I can do without that feature anyway. But this being my first exposer to VB (or VBA?), I'm certainly interested in it. Does it basically provide an interface to where you can do anything programmatically that you would do in excel or an office program manually?

Are there any specific references (online or book) that anyone here would recommend for someone interested in learning it?

thanks again,

jay

 

[Anthem]

I'm not using 2002, but I was curious is the error occuring because the folder that you are trying to save that file to doesn't exist on your machine? Perhaps creating the folder and trying the code again will work correctly. My 2 cents.

 

[jay9333]

Yep, Anthem that was it. Thank you. Man, I could've just played around with that easily enough to figure that out, but I don't know that I would've. Its been about 2 years since I've written any code at all, and I'm not thinking like a programmer (obviously). Soon I'll have to start asking someone to zip up my pants after I use the bathroom too... geez. I feel like a fool, but thanks so much for your help, everyone. I appreciate it very much.

jay