CODEWITHSUNDEEP
vbaexcel
barciewicz
barciewicz

Reputation: 3773

Macro to select first non-empty cell in entire sheet

Suppose there is only one non-empty cell in a sheet and you want a macro to find it. So far I have the following code but am struggling to adjust it so it searches all columns, not only one:

Sub FirstNonEmpty()

Dim ws As Worksheet

Set ws = ActiveSheet

    For Each cell In ws.Columns(1).Cells
        If Not IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
End Sub

Perhaps there is a way to use a variable equal to all columns instead of "1" in "ws.Columns(1).Cells"?

Upvotes: 1

Views: 172

Answers (3)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Or you may give this a try...

Dim Rng As Range
Set Rng = Cells.Find(what:="*", LookIn:=xlValues)
If Not Rng Is Nothing Then
    Rng.Select
End If

Upvotes: 1

Viridian
Viridian

Reputation: 61

You can change the For statement to:

For Each cell In ws.UsedRange.Cells

which has the benefit of not scanning the rows/columns at the end of the worksheet as well.

Upvotes: 0

SJR
SJR

Reputation: 23081

You could use the top formulation if the non-blank cell does not contain a formula, or use xlcelltypeformulas if it's a formula. If you know there is a non-blank, you don't need the On Error. In fact if there is definitely only one you don't need the (1) either.

On Error Resume Next
ActiveSheet.Cells.SpecialCells(xlCellTypeconstants)(1).Select
'or
'ActiveSheet.Cells.SpecialCells(xlCellTypeformulas)(1).Select

Upvotes: 1

Related Questions