Hi,
I have a file (Excel sheet) that fills with any length of records (dynamics) that everyday is always changed the number. what I meant here sometimes a sheet can contain 10 records and another day will contain 20, etc... But the trick is the last row in the sheet is alway contained wording "Total Records: ". I want to have macro delete this row and row before. For example, if the last row is number 20 (Total Records: is displayed at row number 20), then delete the row 20th and 19th.
Question: How do I code it inside the VB?

Thanks
UnixGirl2007

Wait, your name is UnixGirl2007 AND you're asking about Excel? Something's not adding up. Also, what are you going to do in a month? Change your handle? While I'm at it, what are you requesting? Instructions on how to type, or code to preform the operation described?

Sub EndOfStupidQuestions()
    Dim r As Range
    Set r = Cells.Find(What:="Total Records:", After:=Cells(1), _
        LookIn:=xlValues, LookAt:=xlPart)
    Do Until r Is Nothing
        r.Offset(-1, 0).EntireRow.Delete
        r.EntireRow.Delete
        Set r = Cells.FindNext()
    Loop
End Sub

Thanks razor23,
Yes, normally I used some of Unix but sometimes I have to work with something else which in this case there is a macro VB script that I need to add more to exist Unix scripts and I'm not VB person.
Well to tell you the whole story; this VB macro script is for when the user update the Excel files and save them and clicks on this VB macro script then this macro will doing it job by deleting heading, trailing(which I asked in the forum)and some of columns that I don't need and last thing (if it's possible)to add the (updating) date to the last column in the sheet. After these files have been scrubed then I ftp (Unix script) to my Unix server and download to the table. And by the way these files need to be TAB delimiter. I was using '*.txt' extension for the files. Here is the scenario:
Excel spread sheet:
line/col
1 2 3 4 5 6 7 8 9
1 Header1
2 Header2
3 Header3
4 aa bb cc dd ee ff gg hh ii
5 .....
6 .....
7 .....
8
9 Total Records: 4

Here is the action for the VB macro to do:
1. Delete all heading (1,2,3)
2. Delete column 6,7 and 8
3. Add the date (should be Today) to the last column of the sheet.
4. Delete the row 8 and 9.
5. Save the file as *.txt extension to keep the TAB delimited file.

Questions:
1. Can I use one VB macro to do all in one job (there are 3 files)?
2. If using 1 macro to process all files, what about if one or two of the files are not there or empty (most likely is not there)?
Thanks in advance
UnixGirl2007

...I used some of Unix but sometimes I have to work with something else which in this case there is a macro VB script that I need to add more to exist Unix scripts and I'm not VB person.
Nor is English your first language, but that's okay; I think I got the idea.

I generally avoid using Excel if I'm parsing text in scripts; it's powerful, but not universal.

Even if you are dead-set on using Excel as an (overpowered) text parser, I also have misgivings about doing this from a VBA macro in Excel. You'd have to open whatever workbook holds the macro, then run the script. I'd rather have a VBScript that invokes Excel as needed. As such, the following script should be saved as <whatever>.vbs.

But first, questions!
1: Depends on your definition of 'Macro,' but probably. I'd probably break it up into at least two pieces for readability, though.

2: You'd have to handle the possibility, or risk the script bombing out.

Dim files, e, errLvl
 
'Modify this list as needed 
files = Array (_
"1.txt", _
"2.txt", _
"3.txt")
 
Set e = CreateObject("Excel.Application")
e.DisplayAlerts = False
'Feel free to delete this line
e.Visible = True
 
For Each s In files
    errLvl = errLvl * 2 + ParseFile(e, s)
Next
 
'Cleanup
e.Quit
WScript.Quit errLvl
 
Function ParseFile(oExcel, sFile)
    On Error Resume Next
    Dim r, i
    With oExcel
 
    'Try 1 or 3 (instead of 2) if data isn't parsing right
        .Workbooks.OpenText sFile, 2, _
            4, 1, -4142, True, True
           '^---- Step #1 (No Headers)
 
        'File opened okay?
        If Err Then
            Err.Clear
            ParseFile = 1
            Exit Function
        End If
 
        With .Workbooks(1).Worksheets(1)
            'File empty?
            Set r = .Cells.SpecialCells(11).EntireRow.Cells(1)
            If r.Address = "$A$1" And Trim(r.Formula) = "" Then
                ParseFile = 1
                oExcel.Workbooks(1).Close False
                Exit Function
            End If
 
            'Step #3 (Add Date)
            Set r = .Cells.SpecialCells(11).Offset(0,1)
            For Each r In .Range(r, r.EntireColumn.Cells(1))
                r.Formula = Date
            Next
  
            'Step #4 (Delete Footer)
            Set r = .Cells.Find("Total Records:",_
                .Cells(1), -4163, 2)
            Do Until r Is Nothing
                r.Offset(-1, 0).EntireRow.Delete
                r.EntireRow.Delete
                Set r = .Cells.FindNext()
            Loop
 
            'Step #2 (Remove columns 6 - 8)
            For i = 8 To 6 Step -1
                .Columns(i).Delete
            Next
        End With
        'Step #5 (Save)
        .Workbooks(1).Save
        'Final check for problems
        If Err Then _
            ParseFile = 1
        .Workbooks(1).Close False
    End With
End Function