DBern
04-04-2016, 12:07 PM
I have a workbook called Funding Workbook where data from another workbook is pasted into a worksheet within Funding Workbook. While the number of columns is fixed, the number of rows pasted from different users variable. I wrote the following macros to identify the number of rows and create a named range of all of the columns and rows containing data.
Sub Macro2()
'PURPOSE: Different ways to find the last row number of a range
Dim sht As Worksheet
Dim LastRow As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
'Ctrl + Shift + End
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
End Sub
Sub Macro3()
'Purpose: Upon Execution of the above finding and selecting the last completed row with data, create a Named Range to include in a formula.
Range("A2C" & Range("DC2").End(xlDown).Row).Name = "FundingData"
End Sub
This data then feeds another worksheet called Filter This in the same Funding Workbook. My problem is that any field that is blank in the pasted data worksheet creates a #VALUE error in the Filter This worksheet. Therefore, I need a macro that will identify the blank fields in the Paste From worksheet and input a 0 into the fields. I have tried several ways to accomplish this to no avail.
15827
15826
One of the issues that have haunted me is that even though these fields are blank, the paste function does insert some hidden characters in the fields and therefore some functions don't recognize the fields as being blank.
I hope that someone can please help me. I spent 8 hours yesterday trying to find the solution and was unsuccessful.
Please help!
Thank you in advance.
Sub Macro2()
'PURPOSE: Different ways to find the last row number of a range
Dim sht As Worksheet
Dim LastRow As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
'Ctrl + Shift + End
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
End Sub
Sub Macro3()
'Purpose: Upon Execution of the above finding and selecting the last completed row with data, create a Named Range to include in a formula.
Range("A2C" & Range("DC2").End(xlDown).Row).Name = "FundingData"
End Sub
This data then feeds another worksheet called Filter This in the same Funding Workbook. My problem is that any field that is blank in the pasted data worksheet creates a #VALUE error in the Filter This worksheet. Therefore, I need a macro that will identify the blank fields in the Paste From worksheet and input a 0 into the fields. I have tried several ways to accomplish this to no avail.
15827
15826
One of the issues that have haunted me is that even though these fields are blank, the paste function does insert some hidden characters in the fields and therefore some functions don't recognize the fields as being blank.
I hope that someone can please help me. I spent 8 hours yesterday trying to find the solution and was unsuccessful.
Please help!
Thank you in advance.