Select all cells in a range with a certain numerical value
 

I have a column with numbers in it. I want to select all cells with a
certain value, say 1, so that I may delete those cells. It's the method of
selecting the cells I can't figure out (I know how to delete them). Have
fooled around with SpecialCells and Find with no luck. Thanks much.

Select all cells in a range with a certain numerical value
 

Hi
one way to delete them:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = lastrow To 1 Step -1
If Cells(row_index, "A").Value =1 then
Cells(row_index, "A").clearcontents
End If
Next
Application.ScreenUpdating = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany


nathan wrote:
I have a column with numbers in it. I want to select all cells with
a
certain value, say 1, so that I may delete those cells. It's the
method of selecting the cells I can't figure out (I know how to
delete them). Have fooled around with SpecialCells and Find with no
luck. Thanks much.

Select all cells in a range with a certain numerical value
 

Nathan,

This example will delete cells with 1s from column H: Note that you have
three choices at the end of the sub as to what type of deletion to do.

HTH,
Bernie
MS Excel MVP

Sub Delete1s()
Dim c As Range
Dim d As Range
Dim FirstAddress As String
Dim myFindString As String

myFindString = "1"
With ActiveSheet.Range("H:H")
Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
FirstAddress = c.Address
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < FirstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If
End With

'To delete
d.Delete xlUp
'To simply clear formatting and values
d.Clear
'or to simple empty the cells out
d.ClearContents

End Sub


"nathan" wrote in message
...
I have a column with numbers in it. I want to select all cells with a
certain value, say 1, so that I may delete those cells. It's the method
of
selecting the cells I can't figure out (I know how to delete them). Have
fooled around with SpecialCells and Find with no luck. Thanks much.

Select all cells in a range with a certain numerical value
 

Thanks to you both.

"Bernie Deitrick" wrote:

Nathan,

This example will delete cells with 1s from column H: Note that you have
three choices at the end of the sub as to what type of deletion to do.

HTH,
Bernie
MS Excel MVP

Sub Delete1s()
Dim c As Range
Dim d As Range
Dim FirstAddress As String
Dim myFindString As String

myFindString = "1"
With ActiveSheet.Range("H:H")
Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
FirstAddress = c.Address
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < FirstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If
End With

'To delete
d.Delete xlUp
'To simply clear formatting and values
d.Clear
'or to simple empty the cells out
d.ClearContents

End Sub


"nathan" wrote in message
...
I have a column with numbers in it. I want to select all cells with a
certain value, say 1, so that I may delete those cells. It's the method
of
selecting the cells I can't figure out (I know how to delete them). Have
fooled around with SpecialCells and Find with no luck. Thanks much.