| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trying to delete blank rows after file save | Excel Discussion (Misc queries) | |||
| How to delete alternating BLANK rows in .xls file all @ once? | Excel Discussion (Misc queries) | |||
| How delete to end of file; comp keeps adding rows; over 3000! | Excel Worksheet Functions | |||
| How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
| Delete rows in Excel file with macros | Excel Programming | |||
Linear Mode
