Delete some rows at the end of the file

D

Dan

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
 
C

cmdecker2

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
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:
 
D

Dan

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
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






.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to Delete - repost 0
I'm trying to do a "count" function at the end of a dynamic pivot table 0
Help with sorting lists of data. 10
Copy part of text file 4
Lookup and average data in an unsorted list 1
Macro for Copying pasting between different spreadsheets 4
NEW - Groove user offline to others and unable to see anyone outside ofthe LAN. 1
Query Help 0

Top