clean data area

have this solution working

then

1- DTA sheet  clean button don't clean data in row 47 and 46   need clean

2-LINE OUT sheet  need button to clean data and format column B

3-N_spaces sheet need an  button to clean data area  (need clean data and format)
09876.xlsm
ADRIANA PACCOUNTING ASSISTANTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
for issue #1 >> 1- DTA sheet  clean button don't clean data in row 47 and 46   need clean

think it's working fine

SnapShot.png
for issue #2 >> 2-LINE OUT sheet  need button to clean data and format column B

thought I have solved it in your previous question:

CLEAN DATA  COLUMN B
https://www.experts-exchange.com/questions/29076500/CLEAN-DATA-COLUMN-B.html

?

for issue #3 >> 3-N_spaces sheet need an  button to clean data area  (need clean data and format)
what range to be cleaned up?
1
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Ryan Chong  

for issue #3 >> 3-N_spaces sheet need an  button to clean data area  (need clean data and format)
what range to be cleaned up?
123456789.PNG
data range begins from  c14 all the way up
as data can shrink o grown

I have other situatin with this file if you can help i will open a new question for
i need have the Fill BLANK cell Button and function working in DTA sheet
( this button puts an Non Print character so i can work with the data
need but that before anything else ( rigth now is in the last step)
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
data range begins from  c14 all the way up
as data can shrink o grown

I'm trying another approach to handle your case #2 and #3 by using same function.

so we could have something like this:

Sub ClearLineOut(ByVal StartRow As Integer, ByVal LastRow As Integer, ByVal StartCol As String, ByVal EndCol As String)
    With Range(StartCol & StartRow & ":" & EndCol & LastRow)
        .ClearContents
        .Interior.Pattern = xlNone
        .Interior.TintAndShade = 0
        .Interior.PatternTintAndShade = 0
        .Font.ColorIndex = xlAutomatic
        .Font.TintAndShade = 0
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlEdgeLeft).LineStyle = xlNone
        .Borders(xlEdgeTop).LineStyle = xlNone
        .Borders(xlEdgeBottom).LineStyle = xlNone
        .Borders(xlEdgeRight).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
End Sub

Public Function ToColletter(ByVal Collet As Integer) As String
    ToColletter = Split(Cells(1, Collet).Address, "$")(1)
End Function

Open in new window


to solve issue #2, we could have this:

Sub Button1_Click()
    Dim StartRow As Integer, LastRow As Integer
    StartRow = 4
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    Call ClearLineOut(StartRow, LastRow, "B", "B")
End Sub

Open in new window


to solve issue #3, we could have this:

Sub N_SPACES_Button1_Click()
    Dim StartRow As Integer, LastRow As Integer, LastCol As Integer
    StartRow = 14
    LastRow = ActiveSheet.UsedRange.Rows.Count
    LastCol = ActiveSheet.UsedRange.Columns.Count
    Call ClearLineOut(StartRow, LastRow, "C", ToColletter(LastCol))
End Sub

Open in new window


you could also apply similar codes for issue #1 if original code still got problem to clean the cells.
09876_b.xlsm
1
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Ryan Chong

Testing it then
its work great with LINE_OUT sheet  and N_SPACES sheet

but in DTA sheet i get this
after hit one time clean button
ASRE.PNG
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
but in DTA sheet i get this
after hit one time clean button
oh yea... i saw this issue now, weird that it seems working when i tested it earlier today : /

if you don't mind to use my function, then you can try like this:

Sub CleanData()
    Dim StartRow As Integer, LastRow As Integer, LastCol As Integer
    StartRow = 14
    LastRow = ActiveSheet.UsedRange.Rows.Count
    LastCol = ActiveSheet.UsedRange.Columns.Count
    Call ClearLineOut(StartRow, LastRow, "E", ToColletter(LastCol))
End Sub

Open in new window


this cleaning approach is same as your issue #3.
09876_c.xlsm
1
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Ryan Chong

testing still the same issue now in the C column

is any way to have an clean sheet after hit the ckean button ?

234152345.PNG
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
testing still the same issue now in the C column

is any way to have an clean sheet after hit the ckean button ?
try do this...

Sub CleanData()
    Dim StartRow As Integer, LastRow As Integer, LastCol As Integer
    StartRow = 14
    LastRow = ActiveSheet.UsedRange.Rows.Count
    LastCol = ActiveSheet.UsedRange.Columns.Count
    Call ClearLineOut(StartRow, LastRow, "C", ToColletter(LastCol))
End Sub

you may consider to understand the codes as well. The 3rd parameter of function: ClearLineOut is to determine the Start Column to be cleaned up.
0

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

ok how i change that
sorry im not a computer literacy

i don't know how change that ?
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
after open the workbook, try press Alt + F11 to open the Visual Basic Editor.

then under Module1, scroll to the most bottom, and you will find the same codes.

SnapShot.png
1
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Ryan Chong


I did  

i change the code behind the button
then is working as needed now !!
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I did  

i change the code behind the button
then is working as needed now !!
cooool, glad that you found it
1
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Great Job ! Expert !
working as needed
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
after many many many  situation im learning something

Thnaks !!
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.