CODEWITHSUNDEEP
excelvba
Arman Khachaturyan
Arman Khachaturyan

Reputation: 1

How to select the first empty cell in a column on a different sheet using VBA

I am pretty new to using VBA and I need to select the first empty cell in column B on the first sheet (not the active sheet). Whenever I try to activate the code I get an error on:

Cell.Select

What would I need to change in order to resolve this issue? Below is the code I am trying to use:

    Dim ws As Worksheet
    Set ws = Sheets("sheet1")
    For Each Cell In ws.Columns(2).Cells
        If IsEmpty(Cell) = True Then Cell.Select: Exit For
    Next Cell

Upvotes: 0

Views: 952

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You can immediately select the next free cell without looping:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Cells(1, "B").End(xlDown).Offset(RowOffset:=1).Select

Note that this will select the next free cell in column B (B4 in the example below), while the following code will select the free cell after the last used cell in column B (B14 in the example below):

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Cells(ws.Rows.Count, "B").End(xlUp).Offset(RowOffset:=1).Select

But note that using .Select is a bad practice: You might benefit from reading How to avoid using Select in Excel VBA. Only use .Select if you want the user to see that this cell is selected now.

enter image description here

Upvotes: 1

Related Questions