Ranges and Variables

[StefanRSA]

Hi Guys,

Must be getting annoyed with all my N00b questions...


Im trying to use a bit of code to clear a set range of cells. However, Im not sure of how the syntax should be. I have removed where the row references were and replaced them with a variable - y.

I have tried the following but no success:

For y = 10 To 209
If Cells(y, 2) = Employee Then
Range("B:AG").ClearContents
Exit For
End If
Next

Any ideas on the syntax?

Many thanks in advance for your consideration.

 

[Richard Schollar]

Hi Stefan

That would be:

For y = 10 To 209
If Cells(y, 2) = Employee Then
Range("B" & y & ":AG" &  y).ClearContents
Exit For
End If
Next

 

[VoG]

Try

Range("B" & y & ":AG" & y).ClearContents

 

[xld]

For y = 10 To 209
If Cells(y, 2) = Employee Then
Range(Cells(y,"B"),Cells(y,"AG")).ClearContents
Exit For
End If
Next

 

[StefanRSA]

Thanks again, sorry I have not studied VB6, am trying to teach myself as I go along.

I appreciate your help very much.

 

[RoryA]

or:

cells(y,2).resize(,32).ClearContents

 

[Richard Schollar]

Having read further about what it was you were actually attempting to do, I think the following also achieves what you want but much more efficiently:

Dim r As Range
'...
 
With Range("B10:B209")
  Set r = .Find(What:=Employee,After:=.Cells(.Rows.Count,1),SearchDirection:=xlNext)
  If Not r Is Nothing Then Range(r,"AG"&r.Row).ClearContents
End With

 

[StefanRSA]

Ah, so it does... thanks Richard.