How do I select all cells with a value less than 1?

  • Thread starter Thread starter PointerMan
  • Start date Start date
P

PointerMan

I need to eliminate all cells with a value less than 1, but I don't know how.
Can anyone help explain this to me?

Thanks!
 
Hi,

Someone may have a worksheet solution but the only way i know is with a
macro. Right click your sheet tab, view code and paste this in and run it.
Beware it deletes data so try it on a test worksheet

Sub sonic()
Dim MyRange As Range
For Each c In ActiveSheet.UsedRange
If IsNumeric(c) And _
c.Value < 1 _
And c.Value <> vbNullString Then
If MyRange Is Nothing Then
Set MyRange = c
Else
Set MyRange = Union(MyRange, c)
End If
End If
Next
MyRange.ClearContents
End Sub

Mike
 
What do you mean by "eliminate"?

Does this mean to delete the contents of these cells, or to remove
them entirely (and to bring up cells which are further down the sheet
to take their place), or to delete the rows which contain these cells?

How is your data organised? All in one column, or spread over a few
columns?

More information, please.

Pete
 
Hi,

Not enough detail. Are the cells all in one column? Do you want to clear
the cells? Or do you want to delete them? And if you want to delete them do
you want to delete the cells or the entire row they are on? Show us an
example of your data.

If all the cells are in one column and you want to delete their entire rows
then:

1. Select the column of data and choose Data, Filter, AutoFilter
2. Open the drop down for the autofilter and choose Custom
3. From the first box choose "less than" in the second box enter 1
4. Select all the visible cells in the column and press Ctrl+- (control
minus)
5. Either choose Entire Row or Shift cells up depending on whether you want
to remove the individual cells of the entire rows. Or press Del if you just
want to clear the cells.
 
Sorry for the lack of information. I have a spreadsheet with about 125
columns of data that I imported from Access. There are blank cells scattered
throughout the spreadsheet. When I look in a blank cell, it appears empty
but it won't get selected when I use the "Go To" function to select all the
blank cells. If I highlight the cell and hit the delete key, it'll show up
when I use the "Go To" function.

And yes, I essentially want to select the apparently empty cells and delete
their contents so that I can delete the cells and shift everything to the
left.
 
Might be easier to simply know what is in those cells and delete them
that way with Find/Replace. Many times downloaded data is repetitive.

What comes through from Access in those almost-blank cells?
A space? 2 spaces?
Non-breaking spaces?
Is it always the same?
 
Mayhaps the cells contain a space or spaces?

If that's the case you could use this macro to find and delete the extra
spaces.

The do your F5>Special>Blanks

Sub TRIM_EXTRA_SPACES()
Dim Cell As Range
For Each Cell In Selection
If (Not IsEmpty(Cell)) And _
Not IsNumeric(Cell.Value) And _
InStr(Cell.Formula, "=") = 0 _
Then Cell.Value = Application.Trim(Cell.Value)
Next
End Sub


Gord Dibben MS Excel MVP
 
Back
Top