Last row VBA help

[EXCELGPS]

Hi,
I'm after a bit of help please, I wrote this code some 17 years ago and it works fine until now as column D no has formulas in so it is not going to the last row with data in it as I assume it doesn't think the cell is empty because of the formula.
Any way around this please?

The bit causing the problem is highlighted red.

Case Else
    With WS
    
    
         myRow = wsQuote.Range("B65536").End(xlUp).Row
         .Range("J7:K7").Copy
         
        wsQuote.Cells(myRow + 4, 1).PasteSpecial Paste:=xlValues
        wsQuote.Cells(myRow + 4, 1).PasteSpecial Paste:=xlFormats
         
        myRow = wsQuote.Range("B65536").End(xlUp).Row
        .Range("J9:K35").Copy
         
        wsQuote.Cells(myRow + 2, 1).PasteSpecial Paste:=xlValues
        wsQuote.Cells(myRow + 2, 1).PasteSpecial Paste:=xlFormats
         
        myRow = wsQuote.Range("B65536").End(xlUp).Row
         
        LastRow = WS.Range("D65536").End(xlUp).Row
        .Range("B40:D" & LastRow).Copy
         
        wsQuote.Cells(myRow + 2, 1).PasteSpecial Paste:=xlValues
        wsQuote.Cells(myRow + 2, 1).PasteSpecial Paste:=xlFormats
        
        'Set print areas
        LastRow = WS.Range("D65536").End(xlUp).Row
        
        '.Range("B1:H" & LastRow).Select
        
       .PageSetup.PrintArea = "B1:H" & LastRow + 6
         
    End With

 

[MrKowz]

Welcome to the forums!

Try:

LastRow = ws.Range("D:D").Find(What:="*", _
                        Lookat:=xlPart, _
                        LookIn:=xlValues, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row

 

[MrTeeny]

Amend it to

LastRow = WS.Range("D" & Rows.Count).End(xlUp).Row
.Range("B40:D" & LastRow).Copy

 

[Fluff]

Try changing D to another column that will show the last row of data ie

LastRow = WS.Range("[COLOR=#ff0000]B[/COLOR]" & Rows.Count).End(xlUp).Row

 

[EXCELGPS]

Works perfect, thanks for your help.

Welcome to the forums!

Try:

LastRow = ws.Range("D:D").Find(What:="*", _
                        Lookat:=xlPart, _
                        LookIn:=xlValues, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row

 

[EXCELGPS]

Thanks will give it a try.

Amend it to

LastRow = WS.Range("D" & Rows.Count).End(xlUp).Row
.Range("B40:D" & LastRow).Copy

 

[MrKowz]

Works perfect, thanks for your help.

Happy to be of assistance - thank you for the feedback. Have a good one!

 

[EXCELGPS]

That won't work as columns don't finish on same row.

Try changing D to another column that will show the last row of data ie

LastRow = WS.Range("[COLOR=#ff0000]B[/COLOR]" & Rows.Count).End(xlUp).Row