Excel format Worksheet macro 1

[gxh1000]

I've created a macro that formats an Excel worksheet that can vary in length from 100 rows to 500 rows. The code I'm using (Tek-Tips) dynamically resets the last row, but it fails to include the $EOF marker that my C program requires (see sample data). Does anyone know how this can be changed so that the $EOF row is included when the macro formats the data?

Sub CleanBlankRows()
    Dim CurrentEndOfSheet As Integer, MyEndOfSheet As Integer

    CurrentEndOfSheet = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    MyEndOfSheet = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
    Range("A" & MyEndOfSheet + 1 & ":A" & CurrentEndOfSheet).Rows.EntireRow.Delete
    NewEndOfSheet = ActiveSheet.UsedRange.Rows.Count
End Sub

Sample data

6/28/03 C CCC MISW 524 (row 1)
6/23/03 C CCC OHSW 2885
6/24/03 C CCC OHSW 2288
.
.
6/26/03 C CCC IWSW 325
6/27/03 C CCC IWSW -54
6/28/03 C CCC IWSW 383
$EOF (row 500)

 

[luceze]

Try adding an activeworkbook.save line to your code after you delete the rows. In XP at least that resets the used data ranges.

HTH,
Eric

 

[SkipVought]

HI,

I do not know what you are trying to do. When I test your macro, I get the save values for CurrentEndOfSheet & MyEndOfSheet.

Once you have deleted whatever rows you want to delete, then just put the $EOF mark back where it belongs.

Or am I missing sumthin'?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[gxh1000]

Eric - I'm using Excel 97. I tried the save but that didn't work. Thanks.

Skip - The data and the $EOF marker are manually entered to the worksheet. Then the marco is run to format the worksheet and FTP the file to my unix server. Are you saying to manually add the $EOF marker after the format or is there a way to program it into the macro before the FTP?

Thank you.

Gil

 

[SkipVought]

    Cells(NewEndOfSheet + 1, 1).Value = "$EOF"

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com