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?
 
Ryan ChongCommented:
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
 
Ryan ChongCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Ryan ChongCommented:
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
 
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 ChongCommented:
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
 
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 ChongCommented:
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 ChongCommented:
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
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.

All Courses

From novice to tech pro — start learning today.