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

[Sandeman]

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.

[Norman Jones]

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" <Sandeman.25ivmn_1143789005.0439@excelforum-nospam.com> wrote in
message news:Sandeman.25ivmn_1143789005.0439@excelforum-nospam.com...
>
> 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
>

[Sandeman]

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.

[Norman Jones]

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" <Sandeman.25j3yn_1143799816.4162@excelforum-nospam.com> wrote in
message news:Sandeman.25j3yn_1143799816.4162@excelforum-nospam.com...
>
> 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
>