PDA

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



mferrisi
04-16-2007, 08:10 AM
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
04-16-2007, 08:33 AM
This works from a Module
ActiveSheet.Range("J:BJ").Delete
Use this from a sheet
Range("J:BJ").Delete

mferrisi
04-16-2007, 08:38 AM
Thanks Carl. That works when I step through, but again it skipped it when I ran the macro.

xld
04-16-2007, 08:44 AM
That suggest that you don't have the sheet active that you think. Add a worksheet qualifier.

mferrisi
04-16-2007, 08:50 AM
What does that mean? Why does it work on the step-through?

Thank you!

xld
04-16-2007, 08:55 AM
Because somehow you are activating the sheet. Other code may make another sheet active.

mferrisi
04-16-2007, 08:57 AM
What does it mean to add a 'Worksheet Qualifier"? What does that look like?

xld
04-16-2007, 09:05 AM
Worksheets("Sheet1").Columns("J:BJ").Delete

mferrisi
04-16-2007, 09:11 AM
Still skipping....

mferrisi
04-16-2007, 09:32 AM
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

xld
04-16-2007, 09:57 AM
You could throw in a Wait,



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


is 5 secs

mferrisi
04-16-2007, 10:20 AM
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

xld
04-16-2007, 10:36 AM
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
04-16-2007, 11:05 AM
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.:)