how to loop through all used cells in just row 1 and alert?

[elmnas]

hi guys?

How do I loop through just the first row
and all used columns, and then get an alert if the cell is used?

Thank you in advance

[Jan Karel Pieterse]

SUb LoopThroughRow1()
Dim oCell As Range
For Each oCell in ActiveSheet.UsedRange.Rows(1)
If oCell.Value<>"" Then
oCell.Select
MsgBox "Cell " & oCell.Address & " is not empty"
End If
Next
End Sub

[apo]

I might be taking this too literally..


If IIf(Replace(Join(Application.Index(Rows(1).Value, 1, 0), "~"), "~", "") <> "", "Yes", "No") = "Yes" Then MsgBox "Yes.. there is a cell used in the first row!"

[MINCUS1308]

Jan Karel Pieterse,

you have a type mismatch

[MINCUS1308]

PLEASE NOTE:
This code will not identify cells that have a formula forcing them to be blank.


Sub LoopThroughRow1()

For i = 1 To ActiveSheet.UsedRange.Columns.Count
If Cells(1, i) <> "" Then
MsgBox "Cell: " & Cells(1, i).Address & " is used."
End If
Next i

End Sub

[MINCUS1308]

This code will do what you have asked and will identify cells holding formulas forcing them to be blank.


Sub isempt()
For i = 1 To ActiveSheet.UsedRange.Columns.Count
If Not IsEmpty(Cells(1, i)) Then
MsgBox "Cell: " & Cells(1, i).Address & " is used."
End If
Next i
End Sub

[MINCUS1308]

The alert can be dressed up to be more 'Alert' like by replacing the msgbox line with the following code:


MsgBox "Cell: " & Cells(1, i).Address & " is used.", vbCritical, "ALERT - Cell is Occupied."

or


MsgBox "Cell: " & Cells(1, i).Address & " is used.",vbExclamation, "ALERT - Cell is Occupied."

or


MsgBox "Cell: " & Cells(1, i).Address & " is used.", vbInformation, "ALERT - Cell is Occupied."

[Paul_Hossler]

How do I loop through just the first row and all used columns, and then get an alert if the cell is used?


How are you defining 'all used columns'?

A1=123, K1 = 456 then Activesheet.UsedRange = A1:K1

C10 = 123, K10 = 456, then Activesheet.UsedRange = C10:K10

How are you defining 'first row'?

In A1=123, K1 = 456 the first row of Activesheet.UsedRange.Rows(1) = Activesheet.Rows(1) = 1

C10 = 123, K10 = 456, then Activesheet.UsedRange.Rows(1) = Activesheet.Rows(10) = 10


Include Formulas?


One alert per cell, one alert with all cells, or just an alert if any cell is 'Used'

Can you post a small example?

[Jan Karel Pieterse]

Jan Karel Pieterse,

you have a type mismatch

Perhaps if a cell contains an error result. To avoid errors, replace:


If oCell.Value<>"" Then

with


If CStr(oCell.Value)<>"" Then

[snb]

If you are looking for the first empty cell in row 1:


Sub M_snb()
MsgBox Cells(1, Columns.Count).End(-4159).Offset(, 1).Address
End Sub

[elmnas]

AWESOME!

How do I save each used column to a file with current filename + the cell content(text)?

[snb]

You can record a macro doing that manually.