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.
Bob Phillips
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!
Bob Phillips
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?
Bob Phillips
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
Bob Phillips
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
Bob Phillips
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.:)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.