If range F1:K1 are blank, then L1 is "other"
 

I would like a macro that looks at row F1:K1 and if the range is blank,
then the word "other" appears in L1. The data in columns F:K could
range from 50 to several hundred rows down so this needs to be
flexible.

Thanks for your help.


--
Sandeman
------------------------------------------------------------------------
Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440
View this thread: http://www.excelforum.com/showthread...hreadid=528451

If range F1:K1 are blank, then L1 is "other"
 

Hi Sandeman,

Try:

'=============
Public Sub Tester()
Dim SH As Worksheet
Dim rng As Range
Dim rw As Range

Set SH = ActiveSheet '<<==== CHANGE
Set rng = Intersect(SH.UsedRange, Columns("F:K"))

For Each rw In rng.Rows
If Application.CountA(rw.Cells) = 0 Then
Cells(rw.Row, "L").Value = "Other"
End If
Next rw

End Sub
'<<=============


---
Regards,
Norman


"Sandeman" wrote in
message ...

I would like a macro that looks at row F1:K1 and if the range is blank,
then the word "other" appears in L1. The data in columns F:K could
range from 50 to several hundred rows down so this needs to be
flexible.

Thanks for your help.


--
Sandeman
------------------------------------------------------------------------
Sandeman's Profile:
http://www.excelforum.com/member.php...o&userid=32440
View this thread: http://www.excelforum.com/showthread...hreadid=528451

If range F1:K1 are blank, then L1 is "other"
 

Excellent Norman. Thank you very much.

Spoke too soon. The "other" repeats for the length of the spreadsheet.
I'd like it to stop when the data stops. Thanks.


--
Sandeman
------------------------------------------------------------------------
Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440
View this thread: http://www.excelforum.com/showthread...hreadid=528451

If range F1:K1 are blank, then L1 is "other"
 

Hi Sandeman,

Assuming that the last data entry can be determined from column A, try:

'=============
Public Sub Tester2()
Dim SH As Worksheet
Dim rng As Range
Dim rw As Range
Dim LRow As Long
Dim i As Long

Set SH = ActiveSheet '<<==== CHANGE
LRow = SH.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("F1:K" & LRow)

For Each rw In rng.Rows
If Application.CountA(rw.Cells) = 0 Then
Cells(rw.Row, "L").Value = "Other"
End If
Next rw
End Sub
'<<=============

---
Regards,
Norman



"Sandeman" wrote in
message ...

Excellent Norman. Thank you very much.

Spoke too soon. The "other" repeats for the length of the spreadsheet.
I'd like it to stop when the data stops. Thanks.


--
Sandeman
------------------------------------------------------------------------
Sandeman's Profile:
http://www.excelforum.com/member.php...o&userid=32440
View this thread: http://www.excelforum.com/showthread...hreadid=528451