Problem with F5 but not F8

[PKennedyG77]

I have a very simple macro. (Apologies for title error, it is OK with F8 but not OK with F5)
The macro populates a cell which then refreshes the data.
I am then using end(xldown) to find the last row
When I step through this using F8 it works perfectly.
When I use F5 the data does not refresh until the macro is complete and therefore the wrong row is found

Macro is below

Sub RefreshData()
Dim w As Integer
Dim r As Integer
   
    w = InputBox("What week number are you enquiring about." & vbNewLine & " Current Week is " & Range("r2") & "")
    
            Range("G2") = w
   
    ActiveWorkbook.RefreshAll
      
            r = Range("C6").End(xlDown).Row
    
    MsgBox r
   
End Sub

 

[jmacleary]

Hello there. Your problem is probably that the refreshall hasn't finished when you step through quickly. Add a line as shown to your macro:

Sub RefreshData()
Dim w As Integer
Dim r As Integer

w = InputBox("What week number are you enquiring about." & vbNewLine & " Current Week is " & Range("r2") & "")

Range("G2") = w

ActiveWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
r = Range("C6").End(xlDown).Row

MsgBox r

End Sub

 

[PKennedyG77]

Thanks for your prompt response and correction to the issue.
Works a treat

 

[Elaszat]

Hi,

I'm guessing, with the ActiveWorkbook.RefreshAll changes the number of used rows in column C?

If so, you can try putting whatever changes the number into a seperate sub and call it in after you refresh the workbook or instead of refreshing, depending on what you want to do.

I tried putting the sequence into the Worksheet_Change event, but that will result in an infinite loop, clearly.

Regards,
Elaszat

 

[jmacleary]

You're welcome and thanks for the feedback.