0

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Arman Khachaturyan
  • 1

1 Answers1

1

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

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Thank you very much! However, I receive "error 1004" When I try to run the following code: ````Sub CellSelect() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") ws.Cells(1, "B").End(xlDown).Offset(RowOffset:=1).Select End Sub ```` – Arman Khachaturyan May 14 '19 at 16:50
  • The error is on the ```ws.Cells(1, "B").End(xlDown).Offset(RowOffset:=1).Select ``` line – Arman Khachaturyan May 14 '19 at 17:07
  • @ArmanKhachaturyan Because you have no data in column B below B1 – Pᴇʜ May 14 '19 at 19:18