non print character

I have other situatin with this file

need have the Fill BLANK cell Button and function working in DTA sheet

( this button puts an Non Print character in the blanks cell  so i can work with the data

need be in DTA because need  that before anything else  when needed
( rigth now is in the last step)
890765.xlsm
ADRIANA PACCOUNTING ASSISTANTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
probably this will work?

Sub ReplaceBlanks()
    Dim ws As Worksheet
    Dim lngCol As Long
    Dim lngLastRow As Long
    Dim lngLastCol As Long
    Dim cel As Range
    
    Application.ScreenUpdating = False
    
    Set ws = Sheets("DTA")
    With ws
        lngLastRow = .Range("C" & Rows.Count).End(xlUp).Row
        lngLastCol = .Cells(14, Columns.Count).End(xlToLeft).Column
    
        For Each cel In Range(.Cells(14, "C"), .Cells(lngLastRow, lngLastCol))
            If cel = Empty Then
                cel = Chr(2)
            End If
        Next
    End With
    
    Application.ScreenUpdating = True
End Sub

Open in new window

890765_b.xlsm
1
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Ryan Chong

sorry im lost where is the button??

is needed becuse no always have to put the non prin charater in DTA sheet

wil be use when the situation required it !
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
welcome back Martin
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Martin LissOlder than dirtCommented:
The workbook you posted has no data.
1
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Here with some sample data
890765_bBB.xlsm
0
Ryan ChongCommented:
sorry im lost where is the button??

is needed becuse no always have to put the non prin charater in DTA sheet
ooops, I directly modified the click event of "Fill Blank Cells" button of Sheet: N_SPACES instead of adding a new button in Sheet: DTA.

you probably can try this instead in which i added this function:

Sub ReplaceBlanksDTA()
    Dim ws As Worksheet
    Dim lngCol As Long
    Dim lngLastRow As Long
    Dim lngLastCol As Long
    Dim cel As Range
    
    Application.ScreenUpdating = False
    
    Set ws = Sheets("DTA")
    With ws
        lngLastRow = .Range("C" & Rows.Count).End(xlUp).Row
        lngLastCol = .Cells(14, Columns.Count).End(xlToLeft).Column
    
        For Each cel In Range(.Cells(14, "C"), .Cells(lngLastRow, lngLastCol))
            If cel = Empty Then
                cel = Chr(2)
            End If
        Next
    End With
    
    Application.ScreenUpdating = True
End Sub

Open in new window

890765_c.xlsm
1
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
121212123.PNG
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
something is wrong

why ?
aaaaaa.PNG
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
bbbbbbbb.PNG
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
this is the file with the data
890765_cCCC.xlsm
0
Ryan ChongCommented:
there's an issue with :

lngLastCol = .Cells(14, Columns.Count).End(xlToLeft).Column

in function: ReplaceBlanksDTA in which it only find the last column with value, while the cell K14 in Sheet: DTA is empty. hence the code above will pick up column I instead of column K as the last column. so by running function: MoveToN_Spaces, it will pick up from column C to column I instead of from column C to column k.

To solve this, i have to amend the function ReplaceBlanksDTA to:

Sub ReplaceBlanksDTA()
    Dim WS As Worksheet
    Dim lngCol As Long
    Dim lngLastRow As Long
    Dim lngLastCol As Long
    Dim cel As Range
    
    Application.ScreenUpdating = False
    
    Set WS = Sheets("DTA")
    With WS
        lngLastRow = .Range("C" & Rows.Count).End(xlUp).Row
        lngLastCol = GetLastCell(Range("C14:" & ToColletter(WS.UsedRange.Columns.Count) & lngLastRow), xlByColumns).Column
    
        For Each cel In Range(.Cells(14, "C"), .Cells(lngLastRow, lngLastCol))
            If cel = Empty Then
                cel = Chr(2)
            End If
        Next
    End With
    
    Application.ScreenUpdating = True
End Sub

Public Function GetLastCell(InRange As Range, SearchOrder As XlSearchOrder, _
                        Optional ProhibitEmptyFormula As Boolean = False) As Range
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GetLastCell
' By Chip Pearson, chip@cpearson.com, www.cpearson.com
'
' This returns the last used cell in a worksheet or range. If InRange
' is a single cell, the last cell of the entire worksheet if found. If
' InRange contains two or more cells, the last cell in that range is
' returned.
' If SearchOrder is xlByRows (= 1), the last cell is the last
' (right-most) non-blank cell on the last row of data in the
' worksheet's UsedRange. If SearchOrder is xlByColumns
' (= 2), the last cell is the last (bottom-most) non-blank cell in the
' last (right-most) column of the worksheet's UsedRange. If SearchOrder
' is xlByColumns + xlByRows (= 3), the last cell is the intersection of
' the last row and the last column. Note that this cell may not contain
' any value.
' If SearchOrder is anything other than xlByRows, xlByColumns, or
' xlByRows+xlByColumns, an error 5 is raised.
'
' ProhibitEmptyFormula indicates how to handle the case in which the
' last cell is a formula that evaluates to an empty string. If this setting
' is omitted for False, the last cell is allowed to be a formula that
' evaluates to an empty string. If this setting is True, the last cell
' must be either a static value or a formula that evaluates to a non-empty
' string. The default is False, allowing the last cell to be a formula
' that evaluates to an empty string.
'''''''''''''''''''''''''
' Example:
'       a   b   c
'               d   e
'       f   g
'
' If SearchOrder is xlByRows, the last cell is 'g'. If SearchOrder is
' xlByColumns, the last cell is 'e'. If SearchOrder is xlByRows+xlByColumns,
' the last cell is the intersection of the row containing 'g' and the column
' containing 'e'. This cell has no value in this example.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim WS As Worksheet
Dim R As Range
Dim LastCell As Range
Dim LastR As Range
Dim LastC As Range
Dim SearchRange As Range
Dim LookIn As XlFindLookIn
Dim RR As Range

Set WS = InRange.Worksheet

If ProhibitEmptyFormula = False Then
    LookIn = xlFormulas
Else
    LookIn = xlValues
End If

Select Case SearchOrder
    Case XlSearchOrder.xlByColumns, XlSearchOrder.xlByRows, _
            XlSearchOrder.xlByColumns + XlSearchOrder.xlByRows
        ' OK
    Case Else
        Err.Raise 5
        Exit Function
End Select

With WS
    If InRange.Cells.Count = 1 Then
        Set RR = .UsedRange
    Else
       Set RR = InRange
    End If
    Set R = RR(RR.Cells.Count)
    
    If SearchOrder = xlByColumns Then
        Set LastCell = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, MatchCase:=False)
    ElseIf SearchOrder = xlByRows Then
        Set LastCell = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, MatchCase:=False)
    ElseIf SearchOrder = xlByColumns + xlByRows Then
        Set LastC = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, MatchCase:=False)
        Set LastR = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, MatchCase:=False)
        Set LastCell = Application.Intersect(LastR.EntireRow, LastC.EntireColumn)
    Else
        Err.Raise 5
        Exit Function
    End If
End With

Set GetLastCell = LastCell

End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END CODE GetLastCell
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Open in new window

890765_d.xlsm
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Ryan Chong  GREAT JOB

BEST EXPERT  !!
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Ryan Chong

When i hit the one column button
it don't work now
 i get this message

111111.PNG
0
Ryan ChongCommented:
When i hit the one column button
it don't work now
this not really related to this question initially asked.

but.. for this latest error, try re-assign to the correct macro in the button's action:

SnapShot.png
and I also removed the link to 890765_bBB.xlsm

you can try again with attached.

890765_d.xlsm
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.