Compile Error in Finding 1st Blank Line.

[mysterious-dr-x]

If I am very lucky this will be my last question.

This code causes "Compile error: invalid or unqualified reference" & highlights ".Range" in LRs. If I take out the whole LRs section it works fine (except for the obvious fact that the 2nd mention of LR uses the same range determined above, this needs to be different) so this is clearly the problematic section.

What do I need to do to make it work?

Sub UpdateGlobal()
    '
    ' Macro5 UpdateGlobal
    '
    ' Template: find last line, select range & copy
    ' Global: open, find 1st blank line, paste, save & close
    ' Template: clear selection

Application.DisplayAlerts = False

    Dim ws As Worksheet
    Dim LR As Long, lTopRow As Long, LRs As Long
    Set ws = Worksheets("Drawing Register")
    lTopRow = 9
    
    With ws
        LR = .Cells.Find(What:="*", After:=.Range("A1"), _
            LookIn:=xlFormulas, SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, MatchCase:=False).Row
            
        Rows("9:" & LR).Select
        Selection.Copy
    End With
    
    Workbooks.Open FileName:="K:\Testing\Global3.xlsm"
    Sheets("Drawing Register").Select
    
    LRs = .Cells.Find(What:="*", After:=.Range("A1"), _
        LookIn:=xlFormulas, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False).Row
        
    Range("A" & LR + 1).Select
    ActiveSheet.Paste
    Range("A1").Select
    Application.CutCopyMode = False

    ActiveWorkbook.Save
    ActiveWindow.Close
    Range("A1").Select
 
Application.DisplayAlerts = True

End Sub

 

[VoG]

Try

LRs = Cells.Find(What:="*", After:=Range("A1"), _
        LookIn:=xlFormulas, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False).Row

 

[mysterious-dr-x]

Ok, I'll give it a go.

How come that is fine in LR but not in LRs?

 

[Richard Schollar]

Ok, I'll give it a go.

How come that is fine in LR but not in LRs?

You are using a With statement in the LR line but you have closed the With (with End With ) before you get to the LRs line.

 

[VoG]

In the first case it is within the With ws block and . qualifies the range as belonging to ws.

In the second case, your statement is not in a With block so the . causes the error.

 

[mysterious-dr-x]

Ok, that makes sense. Cheers.

Lastly, VoG's solution solves my problem (thanks), but is there anything wrong with the code in the sense that, it works fine, but should technically be done another way?

 

[Richard Schollar]

Do you have a column in your source sheet that will always contain a non-blank value if that row is populated and needs to be extracted? Commonly this could be because of an ID number of the record in the first column? If so, you don't need to use Find but can simply ascertain the last row with data in this particular column and use this to determine the range copied:

Dim lr As Long
 
'Assume col A will always have data
 
lr = Cells(Rows.Count,"A").End(xlUp).Row   'go backwards from bottom of sheet up.
 
Range("9:" & lr).Copy

The same principle can be applied to finding the destination row on the sheet you are transferring the data to.

 

[mysterious-dr-x]

Do you have a column in your source sheet that will always contain a non-blank value if that row is populated and needs to be extracted?

In this particular instance I do not.