VBA code to ignore blank cells with formula when coping

SamCha

New Member
Joined
Nov 23, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
When posting vba code, please use code tags - more info in my signature block below.

Try making this change
Rich (BB code):
lastRow = SourceSht.Cells(Rows.Count, "A").End(xlUp).Row
lastRow = SourceSht.Columns("A").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 
Solution

SamCha

New Member
Joined
Nov 23, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
When posting vba code, please use code tags - more info in my signature block below.

Try making this change
Rich (BB code):
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,391
Messages
5,624,441
Members
416,028
Latest member
aej

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top