VBA code to ignore blank cells with formula when coping

[SamCha]

Hi,

Please can somebody help me with the below code:

Sub Copy_Paste()


Dim DestSht As Worksheet
Dim SourceSht As Worksheet


Set DestSht = ThisWorkbook.Worksheets("Output Template")

Set SourceSht = ThisWorkbook.Worksheets("Input Template")


lastRow = SourceSht.Cells(Rows.Count, "A").End(xlUp).Row


SourceSht.Range("A5:DJ" & lastRow).Copy
DestSht.Range("A2:DJ" & lastRow).PasteSpecial xlPasteValues

End Sub

I am trying to copy rows starting from Row A5 to DJ in a sheet (Input Template) to the last row with data (the data range is not static) to a different sheet (Output Template) in the same workbook. The above code is working fine with copying and pasting the rows in the destination sheet (Output Template), however, the code is also copying the range which are blanks cells containing formulas (""). which is till Row 600.

Please can you suggest the code to copy till the last row having genuine data and avoid copying the cells which are blank with formulas.

Thanks.

 

[Peter_SSs]

When posting vba code, please use code tags - more info in my signature block below.

Try making this change

lastRow = SourceSht.Cells(Rows.Count, "A").End(xlUp).Row
lastRow = SourceSht.Columns("A").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[SamCha]

When posting vba code, please use code tags - more info in my signature block below.

Try making this change

lastRow = SourceSht.Cells(Rows.Count, "A").End(xlUp).Row
lastRow = SourceSht.Columns("A").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Thanks a lot Peter....the code worked for me and also noted on your advice on vba code posting.

 

[Peter_SSs]

Thanks a lot Peter....the code worked for me ...

You are welcome. Thanks for the follow-up.