Select all cells in a range with a certain numerical value

N

nathan

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.
 
F

Frank Kabel

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
 
B

Bernie Deitrick

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
 
N

nathan

Thanks to you both.

Bernie Deitrick said:
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
Click to expand...
 
Top