I'm trying to write a macro that will select a certain range of cells. The
problem is, new rows will be added to and subtracted from the worksheet. But
i still want to be able to have this macro select a certain range, all in one
column and then clear the contents of that range.
Also, I have cells throughout the sheet that are grouped and span the length
of the sheet. These cells have text in them, and that text is constant, will
not change and are group headings.
I'm guessing I have to have the macro select the cells in the specified row
between two constants, then clear the contents and then go on to the next
section. I'm just not completely sure how to do that.
I'm new to VBA and have some VB experience. I understand some of the
constants.
Thanks
I hope I'm not asking too much.

It would help if we could see what you have. Maybe a do.. loop through some
named ranges would do the trick?

"Harlan" wrote:

I'm trying to write a macro that will select a certain range of cells. The
problem is, new rows will be added to and subtracted from the worksheet. But
i still want to be able to have this macro select a certain range, all in one
column and then clear the contents of that range.
Also, I have cells throughout the sheet that are grouped and span the length
of the sheet. These cells have text in them, and that text is constant, will
not change and are group headings.
I'm guessing I have to have the macro select the cells in the specified row
between two constants, then clear the contents and then go on to the next
section. I'm just not completely sure how to do that.
I'm new to VBA and have some VB experience. I understand some of the
constants.
Thanks
I hope I'm not asking too much.

This is what I have so far:

Const BackKitchen As String = "Back Kitchen"
Const FrontLine As String = "Front Line"
Const WalkIn As String = "Walk In"
Const FOH As String = "FOH"
Const RackA As String = "Rack A"
Const RackB As String = "Rack B"
Const RackC As String = "Rack C"
Const RackD As String = "Rack D"
Const RackE As String = "Rack E"
Const RackF As String = "Rack F"
Const SpeedRacks As String = "Speed Racks"
Const DownstairsFreezer As String = "Downstairs Freezer"

Worksheets("Inventory Locations").Activate
Range("C:C").Select

The constants are the names of the headings that span across multiple joined
cells. I'm trying to select all the data in column C that is a number and
delete it.
Thanks

"Office_Novice" wrote:

It would help if we could see what you have. Maybe a do.. loop through some
named ranges would do the trick?

"Harlan" wrote:

I'm trying to write a macro that will select a certain range of cells. The
problem is, new rows will be added to and subtracted from the worksheet. But
i still want to be able to have this macro select a certain range, all in one
column and then clear the contents of that range.
Also, I have cells throughout the sheet that are grouped and span the length
of the sheet. These cells have text in them, and that text is constant, will
not change and are group headings.
I'm guessing I have to have the macro select the cells in the specified row
between two constants, then clear the contents and then go on to the next
section. I'm just not completely sure how to do that.
I'm new to VBA and have some VB experience. I understand some of the
constants.
Thanks
I hope I'm not asking too much.

I am guessing that the range changes and that is what is causeing the trouble?

"Harlan" wrote:

This is what I have so far:

Const BackKitchen As String = "Back Kitchen"
Const FrontLine As String = "Front Line"
Const WalkIn As String = "Walk In"
Const FOH As String = "FOH"
Const RackA As String = "Rack A"
Const RackB As String = "Rack B"
Const RackC As String = "Rack C"
Const RackD As String = "Rack D"
Const RackE As String = "Rack E"
Const RackF As String = "Rack F"
Const SpeedRacks As String = "Speed Racks"
Const DownstairsFreezer As String = "Downstairs Freezer"

Worksheets("Inventory Locations").Activate
Range("C:C").Select

The constants are the names of the headings that span across multiple joined
cells. I'm trying to select all the data in column C that is a number and
delete it.
Thanks

"Office_Novice" wrote:

It would help if we could see what you have. Maybe a do.. loop through some
named ranges would do the trick?

"Harlan" wrote:

I'm trying to write a macro that will select a certain range of cells. The
problem is, new rows will be added to and subtracted from the worksheet. But
i still want to be able to have this macro select a certain range, all in one
column and then clear the contents of that range.
Also, I have cells throughout the sheet that are grouped and span the length
of the sheet. These cells have text in them, and that text is constant, will
not change and are group headings.
I'm guessing I have to have the macro select the cells in the specified row
between two constants, then clear the contents and then go on to the next
section. I'm just not completely sure how to do that.
I'm new to VBA and have some VB experience. I understand some of the
constants.
Thanks
I hope I'm not asking too much.

Actually what is causing the trouble right now is that because I have cells
that are joined and span the entire range of columns (A:E), if I try to
select just column C all columns will become selected. But yes, the other
problem is that rows will be periodically added to and subtracted from the
sheet, but I still want to select only the cells in column C that contain
numbers.



"Office_Novice" wrote:

I am guessing that the range changes and that is what is causeing the trouble?

"Harlan" wrote:

This is what I have so far:

Const BackKitchen As String = "Back Kitchen"
Const FrontLine As String = "Front Line"
Const WalkIn As String = "Walk In"
Const FOH As String = "FOH"
Const RackA As String = "Rack A"
Const RackB As String = "Rack B"
Const RackC As String = "Rack C"
Const RackD As String = "Rack D"
Const RackE As String = "Rack E"
Const RackF As String = "Rack F"
Const SpeedRacks As String = "Speed Racks"
Const DownstairsFreezer As String = "Downstairs Freezer"

Worksheets("Inventory Locations").Activate
Range("C:C").Select

The constants are the names of the headings that span across multiple joined
cells. I'm trying to select all the data in column C that is a number and
delete it.
Thanks

"Office_Novice" wrote:

It would help if we could see what you have. Maybe a do.. loop through some
named ranges would do the trick?

"Harlan" wrote:

I'm trying to write a macro that will select a certain range of cells. The
problem is, new rows will be added to and subtracted from the worksheet. But
i still want to be able to have this macro select a certain range, all in one
column and then clear the contents of that range.
Also, I have cells throughout the sheet that are grouped and span the length
of the sheet. These cells have text in them, and that text is constant, will
not change and are group headings.
I'm guessing I have to have the macro select the cells in the specified row
between two constants, then clear the contents and then go on to the next
section. I'm just not completely sure how to do that.
I'm new to VBA and have some VB experience. I understand some of the
constants.
Thanks
I hope I'm not asking too much.

Try this,
Option Explicit

Sub YourMacro()

Const BackKitchen As String = "Back Kitchen"
Const FrontLine As String = "Front Line"
Const WalkIn As String = "Walk In"
Const FOH As String = "FOH"
Const RackA As String = "Rack A"
Const RackB As String = "Rack B"
Const RackC As String = "Rack C"
Const RackD As String = "Rack D"
Const RackE As String = "Rack E"
Const RackF As String = "Rack F"
Const SpeedRacks As String = "Speed Racks"
Const DownstairsFreezer As String = "Downstairs Freezer"

Worksheets("Inventory Locations").Activate

On Error Resume Next
Range("C:C").Cells.SpecialCells(xlCellTypeConstant s, xlNumbers).ClearContents


End Sub


"Harlan" wrote:

Actually what is causing the trouble right now is that because I have cells
that are joined and span the entire range of columns (A:E), if I try to
select just column C all columns will become selected. But yes, the other
problem is that rows will be periodically added to and subtracted from the
sheet, but I still want to select only the cells in column C that contain
numbers.



"Office_Novice" wrote:

I am guessing that the range changes and that is what is causeing the trouble?

"Harlan" wrote:

This is what I have so far:

Const BackKitchen As String = "Back Kitchen"
Const FrontLine As String = "Front Line"
Const WalkIn As String = "Walk In"
Const FOH As String = "FOH"
Const RackA As String = "Rack A"
Const RackB As String = "Rack B"
Const RackC As String = "Rack C"
Const RackD As String = "Rack D"
Const RackE As String = "Rack E"
Const RackF As String = "Rack F"
Const SpeedRacks As String = "Speed Racks"
Const DownstairsFreezer As String = "Downstairs Freezer"

Worksheets("Inventory Locations").Activate
Range("C:C").Select

The constants are the names of the headings that span across multiple joined
cells. I'm trying to select all the data in column C that is a number and
delete it.
Thanks

"Office_Novice" wrote:

It would help if we could see what you have. Maybe a do.. loop through some
named ranges would do the trick?

"Harlan" wrote:

I'm trying to write a macro that will select a certain range of cells. The
problem is, new rows will be added to and subtracted from the worksheet. But
i still want to be able to have this macro select a certain range, all in one
column and then clear the contents of that range.
Also, I have cells throughout the sheet that are grouped and span the length
of the sheet. These cells have text in them, and that text is constant, will
not change and are group headings.
I'm guessing I have to have the macro select the cells in the specified row
between two constants, then clear the contents and then go on to the next
section. I'm just not completely sure how to do that.
I'm new to VBA and have some VB experience. I understand some of the
constants.
Thanks
I hope I'm not asking too much.

That works perfect. Thanks.

"Office_Novice" wrote:

Try this,
Option Explicit

Sub YourMacro()

Const BackKitchen As String = "Back Kitchen"
Const FrontLine As String = "Front Line"
Const WalkIn As String = "Walk In"
Const FOH As String = "FOH"
Const RackA As String = "Rack A"
Const RackB As String = "Rack B"
Const RackC As String = "Rack C"
Const RackD As String = "Rack D"
Const RackE As String = "Rack E"
Const RackF As String = "Rack F"
Const SpeedRacks As String = "Speed Racks"
Const DownstairsFreezer As String = "Downstairs Freezer"

Worksheets("Inventory Locations").Activate

On Error Resume Next
Range("C:C").Cells.SpecialCells(xlCellTypeConstant s, xlNumbers).ClearContents


End Sub


"Harlan" wrote:

Actually what is causing the trouble right now is that because I have cells
that are joined and span the entire range of columns (A:E), if I try to
select just column C all columns will become selected. But yes, the other
problem is that rows will be periodically added to and subtracted from the
sheet, but I still want to select only the cells in column C that contain
numbers.



"Office_Novice" wrote:

I am guessing that the range changes and that is what is causeing the trouble?

"Harlan" wrote:

This is what I have so far:

Const BackKitchen As String = "Back Kitchen"
Const FrontLine As String = "Front Line"
Const WalkIn As String = "Walk In"
Const FOH As String = "FOH"
Const RackA As String = "Rack A"
Const RackB As String = "Rack B"
Const RackC As String = "Rack C"
Const RackD As String = "Rack D"
Const RackE As String = "Rack E"
Const RackF As String = "Rack F"
Const SpeedRacks As String = "Speed Racks"
Const DownstairsFreezer As String = "Downstairs Freezer"

Worksheets("Inventory Locations").Activate
Range("C:C").Select

The constants are the names of the headings that span across multiple joined
cells. I'm trying to select all the data in column C that is a number and
delete it.
Thanks

"Office_Novice" wrote:

It would help if we could see what you have. Maybe a do.. loop through some
named ranges would do the trick?

"Harlan" wrote:

I'm trying to write a macro that will select a certain range of cells. The
problem is, new rows will be added to and subtracted from the worksheet. But
i still want to be able to have this macro select a certain range, all in one
column and then clear the contents of that range.
Also, I have cells throughout the sheet that are grouped and span the length
of the sheet. These cells have text in them, and that text is constant, will
not change and are group headings.
I'm guessing I have to have the macro select the cells in the specified row
between two constants, then clear the contents and then go on to the next
section. I'm just not completely sure how to do that.
I'm new to VBA and have some VB experience. I understand some of the
constants.
Thanks
I hope I'm not asking too much.