I am trying to set up a macro to clear the data from all the cells on a
worksheet without clearing the column headings on the worksheet. How do I
accomplish this?

If your headers are in rows 1:12

with worksheets("sheet1")
.range("13:" & .rows.count).clearcontents
end with

But do you have stuff you have to keep in (say) column A?



DOOGIE wrote:

I am trying to set up a macro to clear the data from all the cells on a
worksheet without clearing the column headings on the worksheet. How do I
accomplish this?

--

Dave Peterson

No. What I have is a worksheet that collects data for different time periods.
So each time it collects data, it may have a different number of rows of
data. What I want to do is to delete all of the collected data before I start
another data collection. I suppose that I could select all the cells from the
first row of data down to the bottom of the worksheet, but I was hoping to
avoid that.

"Dave Peterson" wrote:

If your headers are in rows 1:12

with worksheets("sheet1")
.range("13:" & .rows.count).clearcontents
end with

But do you have stuff you have to keep in (say) column A?



DOOGIE wrote:

I am trying to set up a macro to clear the data from all the cells on a
worksheet without clearing the column headings on the worksheet. How do I
accomplish this?

--

Dave Peterson

Try selecting all the cells that need to be cleared (click and ctrl-click)
Then Insert|Name|Define
and give that selection a nice name

Then you can type that nice name in the Namebox (to the left of the formula bar)
to select that range.

Then hit the delete key.

If you have lots and lots of cells, then assigning the name this may fail.

You could try:
selecting the cells
hit alt-f8 to see the VBE
hit ctrl-g to see the immediate window

type this and hit enter:
selection.name = "MyList"

(myList is now the name of that selection. Use the name you want.)

And back to excel.
select a cell
and then test it out using the namebox.

DOOGIE wrote:

No. What I have is a worksheet that collects data for different time periods.
So each time it collects data, it may have a different number of rows of
data. What I want to do is to delete all of the collected data before I start
another data collection. I suppose that I could select all the cells from the
first row of data down to the bottom of the worksheet, but I was hoping to
avoid that.

"Dave Peterson" wrote:

If your headers are in rows 1:12

with worksheets("sheet1")
.range("13:" & .rows.count).clearcontents
end with

But do you have stuff you have to keep in (say) column A?



DOOGIE wrote:

I am trying to set up a macro to clear the data from all the cells on a
worksheet without clearing the column headings on the worksheet. How do I
accomplish this?

--

Dave Peterson


--

Dave Peterson