ExcelBanter - Delete some rows at the end of the file

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Delete some rows at the end of the file (https://www.excelbanter.com/excel-programming/310343-delete-some-rows-end-file.html)

Delete some rows at the end of the file

Hi:

Here is my file and the macro that takes the file, dlete
the headings,parses it, format the date, etc and saves it
into a new location; I would need to add the code go at
the end and delete the not valid data. Sorry I am not sure
if the file is readable...

Thank you,

Dan

Sub Lastfran()
'
' Lastfran Macro
' Dan
'
Application.DisplayAlerts = False

ChDir "E:\MCPOLLER\ISS\REPORT"
Workbooks.Open
Filename:="E:\MCPOLLER\ISS\REPORT\LASTFRAN.CSV"
Rows("1:7").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1), Array
(16, 1), Array(27, 1), Array(36, 1), _
Array(41, 1), Array(51, 1), Array(61, 1), Array
(68, 1), Array(75, 1), Array(87, 3), Array( _
93, 1), Array(102, 1)), TrailingMinusNumbers:=True
Columns("K:K").Select
Selection.NumberFormat = "m/d/yyyy"
Range("A1").Select
ChDir "C:\Documents and Settings\dpopescu\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and
Settings\dpopescu\Desktop\LASTFRAN.txt",
FileFormat:=xlText _
, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
Application.DisplayAlerts = True
MsgBox ("Updates Complete")
End Sub
*********************
Sample file:

Node Communication Stats Report 09-17-04
07:45:01 Page: 1

Node Packets Packets Packet Packet Bytes
Bytes Files Files Connect Last Last
Name Sent Rcvd Rexmits Rejects Sent
Rcvd Sent Rcvd Time Connect Time Status
================================================== =========
================================================== =========
======
01189 139 101 0 0 18033
16496 4 5 67 9/17 7:32:57
SUCCESSFUL
01192 104 63 0 0 2454
3102 8 2 52 9/17 3:42:25
SUCCESSFUL
01193 0 0 0 0
0 0 0 0 0 9/14 23:45:18
SUCCESSFUL
01269 0 0 0 0
0 0 0 0 0 9/16 8:52:44
SUCCESSFUL
01303 0 0 0 0
0 0 0 0 0 9/07 3:38:22
SUCCESSFUL
01321 0 0 0 0
0 0 0 0 0 9/16 13:22:09
SUCCESSFUL
01436 85 57 0 0 16249
923 3 1 33 9/17 1:34:18
SUCCESSFUL

Node Communication Stats Report 09-17-04
07:45:01 Page: 2

Node Packets Packets Packet Packet Bytes
Bytes Files Files Connect Last Last
Name Sent Rcvd Rexmits Rejects Sent
Rcvd Sent Rcvd Time Connect Time Status
================================================== =========
================================================== =========
======
02129 0 0 0 0
0 0 0 0 0 6/17 15:37:33
SUCCESSFUL
02138 164 100 0 0 5146
10576 4 7 89 9/17 6:55:20
SUCCESSFUL
02139 0 0 0 0
0 0 0 0 0 8/31 2:18:43
SUCCESSFUL
02141 121 86 0 0 17798
9777 4 4 59 9/17 3:36:56
SUCCESSFUL
02220 60 34 0 0 1084
2810 1 2 21 9/17 7:30:54
SUCCESSFUL
02248 0 0 0 0
0 0 0 0 0 9/16 12:18:52
SUCCESSFUL
02252 0 0 0 0
0 0 0 0 0 9/16 11:12:26
SUCCESSFUL
02296 0 0 0 0
0 0 0 0 0 9/15 22:35:43
SUCCESSFUL

Node Communication Stats Report 09-17-04
07:45:01 Page: 3

Node Packets Packets Packet Packet Bytes
Bytes Files Files Connect Last Last
Name Sent Rcvd Rexmits Rejects Sent
Rcvd Sent Rcvd Time Connect Time Status
================================================== =========
================================================== =========
======
02719 0 0 0 0
0 0 0 0 0 9/04 8:28:45
SUCCESSFUL
02784 113 62 0 0 2322
3237 2 3 59 9/17 6:41:04
SUCCESSFUL
03111 0 0 0 0
0 0 0 0 0 9/06 1:46:11
SUCCESSFUL
03226 100 73 0 0 16408
9549 3 3 51 9/17 4:03:45
SUCCESSFUL
55555 0 0 0 0
0 0 0 0 0

Active Filters:
---------------
NODE_GROUP_FILTER = FRANCHIS FRDCDROM

Delete some rows at the end of the file

Thank you!; I will try...

Dan
-----Original Message-----
Dan,
I am not sure about the other stuff but to delete lines
you can rework this
code to your criteria:

Sub BlankLineDel()
Dim c
Dim iRow, iCol, x As Integer
iRow = Cells.SpecialCells(xlLastCell).Row
iCol = ActiveCell.Column
For x = ActiveCell.Row To iRow
If ActiveCell.Value = "" Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Activate
End If
Next x
End Sub

Carl
"Dan" wrote in
message
...
Hi:

Here is my file and the macro that takes the file, dlete
the headings,parses it, format the date, etc and saves it
into a new location; I would need to add the code go at
the end and delete the not valid data. Sorry I am not sure
if the file is readable...

Thank you,

Dan

Sub Lastfran()
'
' Lastfran Macro
' Dan
'
Application.DisplayAlerts = False

ChDir "E:\MCPOLLER\ISS\REPORT"
Workbooks.Open
Filename:="E:\MCPOLLER\ISS\REPORT\LASTFRAN.CSV"
Rows("1:7").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1), Array
(16, 1), Array(27, 1), Array(36, 1), _
Array(41, 1), Array(51, 1), Array(61, 1), Array
(68, 1), Array(75, 1), Array(87, 3), Array( _
93, 1), Array(102, 1)), TrailingMinusNumbers:=True
Columns("K:K").Select
Selection.NumberFormat = "m/d/yyyy"
Range("A1").Select
ChDir "C:\Documents and Settings\dpopescu\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and
Settings\dpopescu\Desktop\LASTFRAN.txt",
FileFormat:=xlText _
, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
Application.DisplayAlerts = True
MsgBox ("Updates Complete")
End Sub
*********************
Sample file:

Node Communication Stats Report 09-17-04
07:45:01 Page: 1

Node Packets Packets Packet Packet Bytes
Bytes Files Files Connect Last Last
Name Sent Rcvd Rexmits Rejects Sent
Rcvd Sent Rcvd Time Connect Time Status
================================================= =========
=
================================================= =========
=
======
01189 139 101 0 0 18033
16496 4 5 67 9/17 7:32:57
SUCCESSFUL
01192 104 63 0 0 2454
3102 8 2 52 9/17 3:42:25
SUCCESSFUL
01193 0 0 0 0
0 0 0 0 0 9/14 23:45:18
SUCCESSFUL
01269 0 0 0 0
0 0 0 0 0 9/16 8:52:44
SUCCESSFUL
01303 0 0 0 0
0 0 0 0 0 9/07 3:38:22
SUCCESSFUL
01321 0 0 0 0
0 0 0 0 0 9/16 13:22:09
SUCCESSFUL
01436 85 57 0 0 16249
923 3 1 33 9/17 1:34:18
SUCCESSFUL

Node Communication Stats Report 09-17-04
07:45:01 Page: 2

Node Packets Packets Packet Packet Bytes
Bytes Files Files Connect Last Last
Name Sent Rcvd Rexmits Rejects Sent
Rcvd Sent Rcvd Time Connect Time Status
================================================= =========
=
================================================= =========
=
======
02129 0 0 0 0
0 0 0 0 0 6/17 15:37:33
SUCCESSFUL
02138 164 100 0 0 5146
10576 4 7 89 9/17 6:55:20
SUCCESSFUL
02139 0 0 0 0
0 0 0 0 0 8/31 2:18:43
SUCCESSFUL
02141 121 86 0 0 17798
9777 4 4 59 9/17 3:36:56
SUCCESSFUL
02220 60 34 0 0 1084
2810 1 2 21 9/17 7:30:54
SUCCESSFUL
02248 0 0 0 0
0 0 0 0 0 9/16 12:18:52
SUCCESSFUL
02252 0 0 0 0
0 0 0 0 0 9/16 11:12:26
SUCCESSFUL
02296 0 0 0 0
0 0 0 0 0 9/15 22:35:43
SUCCESSFUL

Node Communication Stats Report 09-17-04
07:45:01 Page: 3

Node Packets Packets Packet Packet Bytes
Bytes Files Files Connect Last Last
Name Sent Rcvd Rexmits Rejects Sent
Rcvd Sent Rcvd Time Connect Time Status
================================================= =========
=
================================================= =========
=
======
02719 0 0 0 0
0 0 0 0 0 9/04 8:28:45
SUCCESSFUL
02784 113 62 0 0 2322
3237 2 3 59 9/17 6:41:04
SUCCESSFUL
03111 0 0 0 0
0 0 0 0 0 9/06 1:46:11
SUCCESSFUL
03226 100 73 0 0 16408
9549 3 3 51 9/17 4:03:45
SUCCESSFUL
55555 0 0 0 0
0 0 0 0 0

Active Filters:
---------------
NODE_GROUP_FILTER = FRANCHIS FRDCDROM

.