thanks for your help so far, but im still hanging up at a certain point. i
want this to happen to the whole worksheet, not just a selection, so that may
be part of the problem.

For Each Cell In Selection
If Cell = "total board" Or Cell = "total metal" Or Cell = "ITEM" Or Cell
= "0" _
Or IsNumeric(Cell) Or Cell.HasFormula _
Or IsError(Cell) Then
Cell.Delete
End If
Next Cell

thats the code im trying to use, and theres something wrong with it
somewhere. everything still shows up, even with the code you gave me that
only had "total board" and "total metal" removed. please let me know if you
can be of anymore help. thanks.

You can try the following:

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

This will select everything in your current sheet and then you can run what
you have already for the current selection.

Hope this helps!

-SA

"DFrank" wrote:

thanks for your help so far, but im still hanging up at a certain point. i
want this to happen to the whole worksheet, not just a selection, so that may
be part of the problem.

For Each Cell In Selection
If Cell = "total board" Or Cell = "total metal" Or Cell = "ITEM" Or Cell
= "0" _
Or IsNumeric(Cell) Or Cell.HasFormula _
Or IsError(Cell) Then
Cell.Delete
End If
Next Cell

thats the code im trying to use, and theres something wrong with it
somewhere. everything still shows up, even with the code you gave me that
only had "total board" and "total metal" removed. please let me know if you
can be of anymore help. thanks.

For Each Cell In ActiveSheet.Cells

should do the trick

appreciate the response, but i dont know what happened when iran it. dunno if
it was an error in your code or the other guys, but it turned the values i
wanted to remain into #N/A's and completely reformatted my spreadsheet.

this is what im going back to for now:

Public Sub DeleteandSortStuff()


Sheets("MidStep").Select 'selects the worksheet "midStep" incase
macro ran
'from another worksheet.

Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Delete 'Deletes
error cells


'needs to be code here to delete all terms 'Total Board',
''Total Metal', 'ITEM' and Zeros.

Range("D1:F1686").Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal 'selects the range of remaining
values and
'sorts them, prepares for
transition to other
'worksheet.
End Sub
"StumpedAgain" wrote:

You can try the following:

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

This will select everything in your current sheet and then you can run what
you have already for the current selection.

Hope this helps!

-SA

"DFrank" wrote:

thanks for your help so far, but im still hanging up at a certain point. i
want this to happen to the whole worksheet, not just a selection, so that may
be part of the problem.

For Each Cell In Selection
If Cell = "total board" Or Cell = "total metal" Or Cell = "ITEM" Or Cell
= "0" _
Or IsNumeric(Cell) Or Cell.HasFormula _
Or IsError(Cell) Then
Cell.Delete
End If
Next Cell

thats the code im trying to use, and theres something wrong with it
somewhere. everything still shows up, even with the code you gave me that
only had "total board" and "total metal" removed. please let me know if you
can be of anymore help. thanks.

For Each Cell In ActiveSheet.Cells

Perhaps using...

For Each Cell In ActiveSheet.UsedRange

instead of...

For Each Cell In ActiveSheet.Cells

would involved processing less cells?

Rick

For Each Cell In ActiveSheet.Cells
If Cell = "total board" Or Cell = "total metal" Or Cell = "ITEM" _
Or IsNumeric(Cell) Or Cell.HasFormula _
Or IsError(Cell) Then
Cell.Delete
End If
Next Cell

thats whats in my code, and it was sent into what i thought was gonna be an
infinite loop (for some reason). my screen started flickering and and all the
values on the sheet turned to #N/A.

I appreciate your attempts to remedy this situation, but right now i just
figure it out.

"Jarek Kujawa" wrote:

For Each Cell In ActiveSheet.Cells

should do the trick

you're right Rick

thks

and in case UsedRange does not include xlLastCell could we try

For Each Cell In
ActiveSheet.Range(Cells(1,1),Cells(Activecell.Spec ialcells(xlLastCell).Row,Activecell.Specialcells(x lLastCell).Column)

?

Did you mean to actually delete the cell or just clear it?

Cell.ClearContents


"DFrank" wrote:

For Each Cell In ActiveSheet.Cells
If Cell = "total board" Or Cell = "total metal" Or Cell = "ITEM" _
Or IsNumeric(Cell) Or Cell.HasFormula _
Or IsError(Cell) Then
Cell.Delete
End If
Next Cell

thats whats in my code, and it was sent into what i thought was gonna be an
infinite loop (for some reason). my screen started flickering and and all the
values on the sheet turned to #N/A.

I appreciate your attempts to remedy this situation, but right now i just
figure it out.

"Jarek Kujawa" wrote:

For Each Cell In ActiveSheet.Cells

should do the trick

OK so here's the problem: When you delete a cell, the cell below it takes
its place. Because the program has already looked at that cell, it doesn't
catch the entry that has taken its place and moves on to the next cell.

total metal
ITEM
0
total board

becomes:

ITEM
total board

To fix this I recommend using clear contents instead of delete. See the
following:

Sub delete()

For Each Cell In ActiveSheet.UsedRange
If Cell Like "total board" Or Cell Like "total metal" Or Cell Like "ITEM" Or
Cell Like "0" _
Or IsNumeric(Cell) Or Cell.HasFormula _
Or IsError(Cell) Then
Cell.ClearContents
End If
Next Cell

End Sub

"Rick Rothstein (MVP - VB)" wrote:

For Each Cell In ActiveSheet.Cells

Perhaps using...

For Each Cell In ActiveSheet.UsedRange

instead of...

For Each Cell In ActiveSheet.Cells

would involved processing less cells?

Rick

For Each Cell In ActiveSheet.UsedRange
If Cell Like "total board" Or Cell Like "total metal" Or Cell Like
"ITEM" Or _
Cell Like "0" _
Or IsNumeric(Cell) Or Cell.HasFormula _
Or IsError(Cell) Then
Cell.ClearContents
End If
Next Cell

you forgot an underscore after the first line, but other than that the code
looks legit, but for some reason i get an mismatch error 13.

"StumpedAgain" wrote:

OK so here's the problem: When you delete a cell, the cell below it takes
its place. Because the program has already looked at that cell, it doesn't
catch the entry that has taken its place and moves on to the next cell.

total metal
ITEM
0
total board

becomes:

ITEM
total board

To fix this I recommend using clear contents instead of delete. See the
following:

Sub delete()

For Each Cell In ActiveSheet.UsedRange
If Cell Like "total board" Or Cell Like "total metal" Or Cell Like "ITEM" Or
Cell Like "0" _
Or IsNumeric(Cell) Or Cell.HasFormula _
Or IsError(Cell) Then
Cell.ClearContents
End If
Next Cell

End Sub

"Rick Rothstein (MVP - VB)" wrote:

For Each Cell In ActiveSheet.Cells

Perhaps using...

For Each Cell In ActiveSheet.UsedRange

instead of...

For Each Cell In ActiveSheet.Cells

would involved processing less cells?

Rick

would anyone be up to trying to take a stab at it by having me send it to you?

"DFrank" wrote:

For Each Cell In ActiveSheet.UsedRange
If Cell Like "total board" Or Cell Like "total metal" Or Cell Like
"ITEM" Or _
Cell Like "0" _
Or IsNumeric(Cell) Or Cell.HasFormula _
Or IsError(Cell) Then
Cell.ClearContents
End If
Next Cell

you forgot an underscore after the first line, but other than that the code
looks legit, but for some reason i get an mismatch error 13.

"StumpedAgain" wrote:

OK so here's the problem: When you delete a cell, the cell below it takes
its place. Because the program has already looked at that cell, it doesn't
catch the entry that has taken its place and moves on to the next cell.

total metal
ITEM
0
total board

becomes:

ITEM
total board

To fix this I recommend using clear contents instead of delete. See the
following:

Sub delete()

For Each Cell In ActiveSheet.UsedRange
If Cell Like "total board" Or Cell Like "total metal" Or Cell Like "ITEM" Or
Cell Like "0" _
Or IsNumeric(Cell) Or Cell.HasFormula _
Or IsError(Cell) Then
Cell.ClearContents
End If
Next Cell

End Sub

"Rick Rothstein (MVP - VB)" wrote:

For Each Cell In ActiveSheet.Cells

Perhaps using...

For Each Cell In ActiveSheet.UsedRange

instead of...

For Each Cell In ActiveSheet.Cells

would involved processing less cells?

Rick

I would
;-)))
you can send it to me

thanks, composing now, check your email soon.

"Jarek Kujawa" wrote:

I would
;-)))
you can send it to me

thanks for the help, it works now.

"Jarek Kujawa" wrote:

I would
;-)))
you can send it to me