Clear Contents Macro

[Guest]

I have a workbook which contains one spreadsheet for each month of the
calendar year. The user enters data by rows into the first spreadsheet
month. This data is then linked to the next spreadsheet or calendar month.
The user would then go into the next month and edit the data at a later date.
Since new data is added into rows each month, I have a macro which sorts the
data alphabetically. When a cell is left blank however, a zero is carried
over into the next spreadsheet. Then when the user selects the button to
alphabetize the data rows, all of the rows with zeros rise to the top, and
the rows with text go to the bottom, in alphabetical order, of course. I
figured out how to make the zeros go away in the tools menu, but the text
rows continue to go to the bottom.

Is there a way to write a macro to clear the contents of any cells with a
zero in it and then alphabetize? Any other options?

Thanks for any suggestions.

 

[Bruno Campanini]

I have a workbook which contains one spreadsheet for each month of the
calendar year. The user enters data by rows into the first spreadsheet
month. This data is then linked to the next spreadsheet or calendar
month.
The user would then go into the next month and edit the data at a later
date.
Since new data is added into rows each month, I have a macro which sorts
the
data alphabetically. When a cell is left blank however, a zero is carried
over into the next spreadsheet. Then when the user selects the button to
alphabetize the data rows, all of the rows with zeros rise to the top, and
the rows with text go to the bottom, in alphabetical order, of course. I
figured out how to make the zeros go away in the tools menu, but the text
rows continue to go to the bottom.

Is there a way to write a macro to clear the contents of any cells with a
zero in it and then alphabetize? Any other options?

=======================
Sub Button53_Click()
Dim i

For Each i In [H281:H292]
If i.Value = 0 Then
i.Value = ""
End If
Next

[H281:H292].Sort Key1:=[H281], Order1:=xlAscending

End Sub
====================

Replace [H281:H292] with your data range.
Ciao
Bruno

 

[Guest]

Thank you--the macro does remove all of the zeros, but it does not sort the
data. Any suggestions?

I have a workbook which contains one spreadsheet for each month of the
calendar year. The user enters data by rows into the first spreadsheet
month. This data is then linked to the next spreadsheet or calendar
month.
The user would then go into the next month and edit the data at a later
date.
Since new data is added into rows each month, I have a macro which sorts
the
data alphabetically. When a cell is left blank however, a zero is carried
over into the next spreadsheet. Then when the user selects the button to
alphabetize the data rows, all of the rows with zeros rise to the top, and
the rows with text go to the bottom, in alphabetical order, of course. I
figured out how to make the zeros go away in the tools menu, but the text
rows continue to go to the bottom.

Is there a way to write a macro to clear the contents of any cells with a
zero in it and then alphabetize? Any other options?

=======================
Sub Button53_Click()
Dim i

For Each i In [H281:H292]
If i.Value = 0 Then
i.Value = ""
End If
Next

[H281:H292].Sort Key1:=[H281], Order1:=xlAscending

End Sub
====================

Replace [H281:H292] with your data range.
Ciao
Bruno

 

[Guest]

Please forget my last post. I figured it out--thanks for all of your help.

Thank you--the macro does remove all of the zeros, but it does not sort the
data. Any suggestions?

I have a workbook which contains one spreadsheet for each month of the
calendar year. The user enters data by rows into the first spreadsheet
month. This data is then linked to the next spreadsheet or calendar
month.
The user would then go into the next month and edit the data at a later
date.
Since new data is added into rows each month, I have a macro which sorts
the
data alphabetically. When a cell is left blank however, a zero is carried
over into the next spreadsheet. Then when the user selects the button to
alphabetize the data rows, all of the rows with zeros rise to the top, and
the rows with text go to the bottom, in alphabetical order, of course. I
figured out how to make the zeros go away in the tools menu, but the text
rows continue to go to the bottom.

Is there a way to write a macro to clear the contents of any cells with a
zero in it and then alphabetize? Any other options?

=======================
Sub Button53_Click()
Dim i

For Each i In [H281:H292]
If i.Value = 0 Then
i.Value = ""
End If
Next

[H281:H292].Sort Key1:=[H281], Order1:=xlAscending

End Sub
====================

Replace [H281:H292] with your data range.
Ciao
Bruno