ExcelBanter - VBA code to find first blank cell in a column and activate it

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - VBA code to find first blank cell in a column and activate it (https://www.excelbanter.com/excel-programming/440057-vba-code-find-first-blank-cell-column-activate.html)

VBA code to find first blank cell in a column and activate it

MS XP Pro, MS Office 2007.
Can anyone assist with macro to find the first blank cell in a column and
then to make it the active (selected) cell so that data can be pasted to it
and the row in which it is placed.
I cant find a stable and reliable solution with the Find Blank Cell
procedure in the menu.

Answer: VBA code to find first blank cell in a column and activate it

Sure, I can help you with that! Here's a VBA code that will find the first blank cell in column A and activate it:

Formula:




Sub FindFirstBlankCell()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row 'finds the last row with data in column A
    
    For i = 1 To LastRow 'loops through each row in column A
        If Cells(i, "A").Value = "" Then 'checks if the cell is blank
            Cells(i, "A").Select 'selects the first blank cell
            Exit For 'exits the loop once the first blank cell is found
        End If
    Next i
End Sub

To use this code, open your Excel workbook and press Alt + F11 to open the VBA editor. Then, click on Insert Module and paste the code above into the module. Finally, press F5 to run the macro.

This code will find the first blank cell in column A and activate it, so you can paste your data into it. If you need to find the first blank cell in a different column, simply change the "A" in the code to the appropriate column letter.

Open Excel workbook
Press Alt + F11 to open the VBA editor
Click on Insert Module
Paste the code into the module
Press F5 to run the macro

VBA code to find first blank cell in a column and activate it

When you say first blank cell, do you mean -
From the top down, the first blank cell with possibly other non blank cells
below that blank cell.
or -
From the bottom up, the cell below the first non-blank cell.

Regards,
Peter T

"Wes_A" wrote in message
...
MS XP Pro, MS Office 2007.
Can anyone assist with macro to find the first blank cell in a column and
then to make it the active (selected) cell so that data can be pasted to
it
and the row in which it is placed.
I cant find a stable and reliable solution with the Find Blank Cell
procedure in the menu.

VBA code to find first blank cell in a column and activate it

Dim FirstBlankCell as Range
Set FirstBlankCell=Range("A" & rows.Count).end(xlup).offset(1,0)
FirstBlankCell.Activate

Note that you do not need to select a cell to paste data into it, and it is
slowing down your macro.

Look at this:

Worksheets("Sheet2").Range("A2:D2").copy Destination:=FirstBlankCell

Regards,
Per

"Wes_A" skrev i meddelelsen
...
MS XP Pro, MS Office 2007.
Can anyone assist with macro to find the first blank cell in a column and
then to make it the active (selected) cell so that data can be pasted to
it
and the row in which it is placed.
I cant find a stable and reliable solution with the Find Blank Cell
procedure in the menu.

VBA code to find first blank cell in a column and activate it

How about:

Sub FindFirstBlank()
Dim r1 As Range, r2 As Range
Set r1 = Intersect(Range("B:B"), Cells.SpecialCells(xlCellTypeBlanks))
Set r2 = Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
If r1 Is Nothing Then
r2.Select
Else
r1.Select
End If
End Sub
--
Gary''s Student - gsnu201001

"Wes_A" wrote:

MS XP Pro, MS Office 2007.
Can anyone assist with macro to find the first blank cell in a column and
then to make it the active (selected) cell so that data can be pasted to it
and the row in which it is placed.
I cant find a stable and reliable solution with the Find Blank Cell
procedure in the menu.

VBA code to find first blank cell in a column and activate it

This is a correction:

Sub FindFirstBlank()
Dim r1 As Range, r2 As Range
Set r1 = Intersect(Range("B:B"), Cells.SpecialCells(xlCellTypeBlanks))
Set r2 = Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
If r1 Is Nothing Then
r2.Select
Else
r1(1).Select
End If
End Sub

--
Gary''s Student - gsnu201001

"Wes_A" wrote:

MS XP Pro, MS Office 2007.
Can anyone assist with macro to find the first blank cell in a column and
then to make it the active (selected) cell so that data can be pasted to it
and the row in which it is placed.
I cant find a stable and reliable solution with the Find Blank Cell
procedure in the menu.