Hi Rohit,
Here's one way to do it:
Dim ecode As Range
Dim rngTable As Range
With ActiveSheet
Set rngTable = .Range(.Range("H4"), _
.Range("H65536").End(xlUp))
End With
For Each ecode In rngTable
With ecode
If Len(.Text) 0 Then
.Offset(, -3).Value = Sheets _
("Site Parameters").Range("N11").Value
End If
End With
Next ecode
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/
* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
"Rohit Thomas" wrote in message
...
I have the following code to look through a range of cells
and populate another range if the text is not blank. My
code searches range H4:H201 everytime. Sometimes text may
only be in range H4:H20 or H4:H100. How can I make this
code more efficient to only look in the cells that have
text in column H starting with H4?
Dim ecode As Range
Range("H4:H110").Select
For Each ecode In Selection
With ecode
If Len(.Text) 0 Then
.Offset(, -3).Value = Sheets _
("Site Parameters").Range("N11").Value
End If
End With
Next ecode
Thanks,
Rohit