Finding blank cells in named range and input 0

[DBern]

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.

[jolivanes]

Would it not be better to find out what Character it is and then delete it?

BTW, to find out what kind of character you're dealing with, select one of the cells and run this.

Sub Get_Strange_Char_ASCII_Code()
MsgBox Asc(Selection.Value)
End Sub


If the characters happen to be just regular single spaces (Char 32), this should get rid of them

Sub Delete_Single_Space()
Dim c As Range
For Each c In Sheets("Sheet1").UsedRange '<---- Change as required
On Error Resume Next
If Len(c) = 1 And Asc(c.Value) = 32 Then c.Value = ""
On Error GoTo 0
Next c
End Sub

[DBern]

Thank you for your reply. The hidden character is asc 32. How would you suggest finding these "blank" cells within a named range.

[jolivanes]

Try

Sub Delete_Single_Space()
Dim c As Range
For Each c In Sheets("Sheet1").Range("FundingData") '<---- Is this the Named Range? If not, change as required.
On Error Resume Next
If Len(c) = 1 And Asc(c.Value) = 32 Then c.Value = ""
On Error GoTo 0
Next c
End Sub

[DBern]

Thank you for another reply. I'll try it tomorrow and let you know. I really appreciate the help.