Link to home
Start Free TrialLog in
Avatar of ADRIANA P
ADRIANA PFlag for United States of America

asked on

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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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
Avatar of ADRIANA P

ASKER

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 !
welcome back Martin
The workbook you posted has no data.
Here with some sample data
890765_bBB.xlsm
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
User generated image
something is wrong

why ?
User generated image
User generated image
this is the file with the data
890765_cCCC.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ryan Chong  GREAT JOB

BEST EXPERT  !!
Ryan Chong

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

User generated image
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:

User generated image
and I also removed the link to 890765_bBB.xlsm

you can try again with attached.

890765_d.xlsm