Macro is removing and Adding Rows

You're deleting row 4, which is why your visible range keeps shrinking. You can compensate for that by inserting a row - I picked 29, but it just needs to be after your data. The following shows some of what can be cleanded up from macro-created code.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro1()
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">' Macro1 Macro</SPAN>
<SPAN style="color:#007F00">' Macro recorded 11/26/2003 by Mike Duffy</SPAN>
<SPAN style="color:#007F00">'</SPAN>

Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">With</SPAN> ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\stats.txt", _
Destination:=Range("A4"))
    .Name = "stats"
    .FieldNames = <SPAN style="color:#00007F">True</SPAN>
    .RowNumbers = <SPAN style="color:#00007F">False</SPAN>
    .FillAdjacentFormulas = <SPAN style="color:#00007F">False</SPAN>
    .PreserveFormatting = <SPAN style="color:#00007F">True</SPAN>
    .RefreshOnFileOpen = <SPAN style="color:#00007F">False</SPAN>
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = <SPAN style="color:#00007F">False</SPAN>
    .SaveData = <SPAN style="color:#00007F">True</SPAN>
    .AdjustColumnWidth = <SPAN style="color:#00007F">True</SPAN>
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = <SPAN style="color:#00007F">False</SPAN>
    .TextFilePlatform = xlWindows
    .TextFileStartRow = 1
    .TextFileParseType = xlFixedWidth
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = <SPAN style="color:#00007F">False</SPAN>
    .TextFileTabDelimiter = <SPAN style="color:#00007F">True</SPAN>
    .TextFileSemicolonDelimiter = <SPAN style="color:#00007F">False</SPAN>
    .TextFileCommaDelimiter = <SPAN style="color:#00007F">False</SPAN>
    .TextFileSpaceDelimiter = <SPAN style="color:#00007F">False</SPAN>
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileFixedColumnWidths = Array(17, 8, 8, 8, 8, 8, 9, 5)
    .Refresh BackgroundQuery:=<SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Rows("4:4").Delete Shift:=xlUp
Columns("H:I").Delete Shift:=xlToLeft
Columns("F:F").Insert Shift:=xlToRight
Range("F4").FormulaR1C1 = "Avg"
Range("F5").FormulaR1C1 = "Handle"
Range("F6").FormulaR1C1 = "Time "
Range("F7").FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
Range("F7").AutoFill Destination:=Range("F7:F30"), Type:=xlFillDefault
Columns("F:F").EntireColumn.AutoFit
Range("F7:F30").Select                  <SPAN style="color:#007F00">' Not sure about this, may be able to delete</SPAN>
ActiveWindow.SmallScroll Down:=-15      <SPAN style="color:#007F00">' these two lines of code</SPAN>
Range("F8").ClearContents
Rows("29:29").EntireRow.Insert          <SPAN style="color:#007F00">' New line of code</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>



</FONT>

¡Pura vida!

Greg,

Thanks a lot for the response I tried it but every time I run it I get to prevent possible loss of data Microsoft Excel can not shift No blank cells off the sheet try to delete or clear the cells to the right and Below your data.

I have erased everything on the Work sheet and I still get the same error Any Ideas.

Mike,

I have only used the Query tables function once or twice, so I am not very familiar with what types of restictions it imposes. Have you stepped through your code to confirm which line is generating the error?

Greg Truby said:

Mike,

I have only used the Query tables function once or twice, so I am not very familiar with what types of restictions it imposes. Have you stepped through your code to confirm which line is generating the error?

Click to expand...

This is what I found running the code in brake mode as soon as I moved from Selection.Delete Shift:=xlUp to Columns("H:I").Select (this is after the Import of the text file) The "error" happened Any help with this would really be appreciated because of my limited knowledge with VBA I really have no Idea Why this would be happening
End With
Rows("4:4").Select
Selection.Delete Shift:=xlUp
Columns("H:I").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Range("F4").Select
ActiveCell.FormulaR1C1 = "Avg"
Range("F5").Select
ActiveCell.FormulaR1C1 = "Handle"
Range("F6").Select
ActiveCell.FormulaR1C1 = "Time "
Range("F7").Select
Columns("F:F").EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
Range("F7").Select
Selection.AutoFill Destination:=Range("F7:F30"), Type:=xlFillDefault
Range("F7:F30").Select
ActiveWindow.SmallScroll Down:=-15
Range("F8").Select
Selection.ClearContents
Application.ScreenUpdating = True
End Sub

Edit to fix small typo

Mike,

Looking at the parameters you pass to the query, I created a mock up text file to import. I was able to run the code you posted a couple of times on the same page with no problems (other than it making a real mess - but no errors). How long is your input file?

Again - I apologize, I'm on really thin ice here - Query tables are not my forte. Perhaps with this bump, an MVP will see this. In the meantime I'm going to see if there a way to get an MVP to help here.

Greg

Greg Sorry I misunderstood your post about the error I think the error I get was after I added your Line of code listed below When I remove this line of code it will run but My original issue happens when this code runs
Selection.Delete Shift:=xlUp to Columns("H:I").Select Columns("H:I").Select


Rows("29:29").EntireRow.Insert ' New line of code

Hope this helps and my apologies on the misunderstanding

thanks in advance for any help you or any one else can give me

I'm still at an impass, I tried running my code as well and it worked fine. I "shot off a flare" to see if we can get one of the all stars to stop by and take a look.

Discúlpeme por no haberle ayudado más.

Also Forgot to add if you or any one wanted me to send the file with the txt file to test It will be More than happy to

thanks

Greg Truby said:

I'm still at an impasse, I tried running my code as well and it worked fine. I "shot off a flare" to see if we can get one of the all stars to stop by and take a look.

Discúlpeme por no haberle ayudado más.

Click to expand...

Greg,
Mil gracias!!!!!!!!

I found the error in the code and you were a Big help First off I am not sure what happend to my sheet but when you said it worked fine I tested the Code In a new sheet second sheet it worked fine even with the new code you added but now it was adding an extra Row every time I ran it and Hiding row 30
so I changed this Selection.Delete Shift:=xlUp
to this Selection.ClearContents
adjusted a few of the cell references and it's working like a charm


Thanks for all your help. And I hope this thread my help some one else out in the future

here is a new tico phrase fro you "Pura Birra"