Clear Contents Macro

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.

"SJC" <[email protected]> wrote in message
news:[email protected]...
>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

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

"Bruno Campanini" wrote:

> "SJC" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>

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

"SJC" wrote:

> Thank you--the macro does remove all of the zeros, but it does not sort the
> data. Any suggestions?
>
> "Bruno Campanini" wrote:
>
> > "SJC" <[email protected]> wrote in message
> > news:[email protected]...
> > >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
> >
> >
> >