Prev: Another '03 vs '07 Add-in Menu Question
Next: cpu usage when excel is in taskbar
From: Wes_A on 28 Feb 2010 03:46
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.
From: Peter T on 28 Feb 2010 04:09
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(a)discussions.microsoft.com> wrote in message
news:06D72679-69DB-4591-A520-80CA75F71B2A(a)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.


From: Per Jessen on 28 Feb 2010 04:11
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(a)discussions.microsoft.com> skrev i meddelelsen
news:06D72679-69DB-4591-A520-80CA75F71B2A(a)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.

From: Gary''s Student on 28 Feb 2010 09:24
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.
From: Gary''s Student on 28 Feb 2010 09:28
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.
 | 
Pages: 1
Prev: Another '03 vs '07 Add-in Menu Question
Next: cpu usage when excel is in taskbar