View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 6,953
Default ive been stuck for 2 days

If your using a version of excel prior to xl2000 - not likely - but in that
case, the behavior of Offset is different. In those versions you would use:

Sub ClearO()
Dim rngBlank As Range
Dim rng1 as Range
On Error Resume Next
Set rngBlank = Range("A2:A5002").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rngBlank Is Nothing Then
MsgBox "nothing to clear"
Else
set rng1 = Intersect(rngBlank.EntireRow,Range("O:O"))
rng1.ClearContents
End If
End Sub

Just as in Jim's original post, this assumes the cells in column A are
actually empty and don't just appear empty, but are not.

--
Regards,
Tom Ogilvy

"Jim Thomlinson" wrote:

This should be close...

Sub ClearO()
Dim rngBlank As Range

On Error Resume Next
Set rngBlank = Range("A2:A5002").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rngBlank Is Nothing Then
MsgBox "nothing to clear"
Else
rngBlank.Offset(0, 14).ClearContents
End If
End Sub
--
HTH...

Jim Thomlinson


" wrote:

i have a single page worksheet with various information that
automaticly changes and sorts based on another worksheet. whith that
said here is my issue

i need a script that checks wether there is information in colum (A).

if there is information in (A) then leave (O) alone. if not then clear
(O)

example if (A7) = blank, clear (O7)

my range is (A2:A5002) and (O2:O5002)

any insight would be great