About Us

Nathan

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Select a range of cells	wx4usa	Excel Discussion (Misc queries)	2	December 23rd 09 07:32 PM
syntax for countif when cells fall within a numerical range	Tom L	Excel Worksheet Functions	5	May 31st 08 03:22 AM
When entering data into a range of cells, select the entire range.	Q	Excel Discussion (Misc queries)	0	September 26th 07 04:36 AM
Select Range of Cells	programmingrookie	Excel Discussion (Misc queries)	2	August 11th 05 07:13 PM
select a range using "cells()"	fullers	Excel Worksheet Functions	2	July 6th 05 06:00 PM

Menu

About Us