Solved: Code works on step through, but skips when run...

[mferrisi]

Range("J:BJ").Select
Selection.Delete

When I step through the code, the selected cells delete. However, when I run the program, they do not. Am I missing something obvious?

Thank you

[Carl A]

This works from a Module
ActiveSheet.Range("J:BJ").Delete
Use this from a sheet
Range("J:BJ").Delete

[mferrisi]

Thanks Carl. That works when I step through, but again it skipped it when I ran the macro.

[Bob Phillips]

That suggest that you don't have the sheet active that you think. Add a worksheet qualifier.

[mferrisi]

What does that mean? Why does it work on the step-through?

Thank you!

[Bob Phillips]

Because somehow you are activating the sheet. Other code may make another sheet active.

[mferrisi]

What does it mean to add a 'Worksheet Qualifier"? What does that look like?

[Bob Phillips]

Worksheets("Sheet1").Columns("J:BJ").Delete

[mferrisi]

Still skipping....

[mferrisi]

Hi xld,

It looks as though there is a delay in the downloading of the data, (a message appears on the top of the sheet that says "importing data...") and after about a second, the data is brought in. Thus, the deleting of the cells was occuring, just not after the data was imported to the sheet.

So, this brings up another question-- How can I delay my code so that it does not continue until the data is imported?

Thank you,

Matt

[Bob Phillips]

You could throw in a Wait,



waittime = TimeSerial(Hour(Now), Minute(Now), Second(Now) + 5)
Application.Wait waittime


is 5 secs

[mferrisi]

Oddly, the data doesn't import until after the wait is over and produces an error. The instant I hit 'Debug' the data posts.

.CommandText = Array(W_run)
.name = "Query from MTPS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
' .RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With

waittime = TimeSerial(Hour(Now), Minute(Now), Second(Now) + 5)
Application.Wait waittime

ThisWorkbook.Sheets("Development").Columns("J:BJ").Delete

[Bob Phillips]

Sorry, that was probably a silly suggestion. A better suggestion might be to use Ontime to run a macro contatining the rest of the code.

[Norie]

Matt

Why not just don't import these columns/fields in the first place?

I don't know if that's possible since I've not seen your query or any data but I just thought I'd throw the suggestion in.:)