Find first blank cell

[Guest]

Hi,

I have two sheets of data I would like to merge. The problem is that both
are variable. I would like to past the second sheet of data below the first.
Can you please let me know the code to find the first blank cell in the first
sheet?

Thanks
Rui

 

[Norman Jones]

Hi Rui,

Try:

Dim NextCell As Range

Set NextCell = Sheets("Sheet1").Cells(Rows.Count, 1). _
End(xlUp)(2)

 

[Jim May]

Norman:
I see this all too often, that is the "(2)" at the very end of your
code.
What does it instruct VB to do?
TIA,

 

[Alan Beban]

Norman:
I see this all too often, that is the "(2)" at the very end of your
code.
What does it instruct VB to do?
TIA,

See Item Property (Range Object) in Microsoft Visual Basic Help. One
thing the Help does not clarify is that the "Item" can be omitted; i.e.,
Range("A1:A10").Item(1) can be expressed Range("A1:A10")(1). Another
slight error in the Help is that it indicates that if the name of the
relevant range is "Range", then Range.Item(2) will return the cell
immediately to the right of the upper left cell; that's true only if
Range has more than one column; if it does not, then you can refer to
the cell immediately to the right of the upper left cell with
Range.Item(1,2) [or, as mentioned above, Range(1,2)]; ositive single
indexing can be used to refer only to cells within and directly below
the specified range. Negative single indexing is somewhat idiosyncratic
and not worth elaborating in this thread.

By the way, the code provided by Norman Jones will return not
necessarily the first blank cell, but the first blank cell after the
last nonblank cell in Column A. This will be ok only if there are no
blanks in the data.

More generally, if Cell A1 and A2 are not blank, you might consider

Set NextCell = Sheets("Sheet1").Range("A1").End(xlDown)(2)

Otherwise you will have to define the possible conditions and provide
for them in order to assign the first blank cell in the column; e.g.,

With Sheets("Sheet1")
If .Range("A1") = "" Then
Set NextCell = .Range("A1")
ElseIf .Range("A2") = "" Then
Set NextCell = .Range("A2")
Else
Set NextCell = .Range("A1").End(xlDown)(2)
End If
End With

Alan Beban

 

[Jim May]

Alan: Thanks for indepth explanation.

Jim May

 

[Alan Beban]

I'm afraid in the discussion in my previous response I let myself get
sucked into another mistake in the Help. I referred to the Help's
reference Range.Item(2) as though "Range" was being used as the name of
a range; Range.Item(2) is in the form of a range assigned to a variable
named "Range", but this is actually not permitted. Range is a Reserved
keyword and not available as a variable name. In my discussion below
the use of, e.g., Range.Item(2) or Range(2) should have been to
something like MyRange.Item(2) or MyRange(2) where "MyRange" is the name
of a variable to which a range has been assigned.

Sorry for any confusion.

Alan Beban

Norman:
I see this all too often, that is the "(2)" at the very end of your
code.
What does it instruct VB to do?
TIA,

See Item Property (Range Object) in Microsoft Visual Basic Help. One
thing the Help does not clarify is that the "Item" can be omitted; i.e.,
Range("A1:A10").Item(1) can be expressed Range("A1:A10")(1). Another
slight error in the Help is that it indicates that if the name of the
relevant range is "Range", then Range.Item(2) will return the cell
immediately to the right of the upper left cell; that's true only if
Range has more than one column; if it does not, then you can refer to
the cell immediately to the right of the upper left cell with
Range.Item(1,2) [or, as mentioned above, Range(1,2)]; positive single
indexing can be used to refer only to cells within and directly below
the specified range. Negative single indexing is somewhat idiosyncratic
and not worth elaborating in this thread.

By the way, the code provided by Norman Jones will return not
necessarily the first blank cell, but the first blank cell after the
last nonblank cell in Column A. This will be ok only if there are no
blanks in the data.

More generally, if Cell A1 and A2 are not blank, you might consider

Set NextCell = Sheets("Sheet1").Range("A1").End(xlDown)(2)

Otherwise you will have to define the possible conditions and provide
for them in order to assign the first blank cell in the column; e.g.,

With Sheets("Sheet1")
If .Range("A1") = "" Then
Set NextCell = .Range("A1")
ElseIf .Range("A2") = "" Then
Set NextCell = .Range("A2")
Else
Set NextCell = .Range("A1").End(xlDown)(2)
End If
End With

Alan Beban