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

  • Follow


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.
0
Reply Utf 2/28/2010 8:46:01 AM

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" <WesA@discussions.microsoft.com> wrote in message 
news:06D72679-69DB-4591-A520-80CA75F71B2A@microsoft.com...
> 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. 


0
Reply Peter 2/28/2010 9:09:46 AM


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" <WesA@discussions.microsoft.com> skrev i meddelelsen 
news:06D72679-69DB-4591-A520-80CA75F71B2A@microsoft.com...
> 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. 

0
Reply Per 2/28/2010 9:11:00 AM

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.
0
Reply Utf 2/28/2010 2:24:01 PM

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.
1
Reply Utf 2/28/2010 2:28:01 PM

4 Replies
8640 Views

(page loaded in 0.095 seconds)


Reply: