Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

selecting first available blank row in column

Posted by richey on February 08, 2002 9:51 AM
Hi
I've got this code running, which although works takes time. I need to know how I can say
Sheets("User Survey_new").Select
and then find the first empty row and put the cursor in the cell within column a
at the moment I've got the following which works but loops through and takes time. if the first empty row is say row 400 it takes time.
any help much appreciated
richey

Worksheets("User Survey_new").Cells(1, 1).Select
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Paste


Check out our Excel VBA Resources

Re: selecting first available blank row in column

Posted by Ken on February 08, 2002 10:44 AM



Re: selecting first available blank row in column

Posted by Ken on February 08, 2002 10:45 AM


Re: selecting first available blank row in column

Posted by Ken on February 08, 2002 10:52 AM
Try this....
Dim NextRow As Long
NextRow = Range("A65536").End(xlUp).Row + 1
Cells(NextRow, 1) = "Whatever"
Ken


Re: selecting first available blank row in column

Posted by Ivan F Moala on February 08, 2002 11:07 AM
Or another way......

Range("yourrangeaddress").Copy Destination:=Sheets("User Survey_new").Range("A65536").End(xlUp).Offset(1, 0)


Ivan


Re: selecting first available blank row in column

Posted by Mark W. on February 09, 2002 3:37 PM
Sub Macro1()
Columns("A:A").Select
Selection.SpecialCell (xlCellTypeBlanks).Select
ActiveCell.Select
End Sub


Or ....

Posted by Menelaus on February 09, 2002 3:54 PM
Columns("A:A").SpecialCells(xlCellTypeBlanks)(1, 1).Select


Excellent!!! [nt]

Posted by Mark W. on February 09, 2002 4:15 PM


Re: selecting first available blank row in column

Posted by richey on February 11, 2002 2:36 AM
Cheers guys but I get a message saying 'object doesn't support this property or method' error for the selection.specialcell line.... I'm using Excel 97?


Re: selecting first available blank row in column

Posted by Menelaus on February 11, 2002 4:13 PM
Perhaps you should post the exact code that is producing the error. Can't think why it's not working unless Excel 97 doesn't support the SpecialCells method (can't remember).

You could use this instead if you like :-

If Cells(1, 1) = "" Then
Cells(1, 1).Select
ElseIf Cells(2, 1) = "" Then
Cells(2, 1).Select
Else
Cells(1, 1).End(xlDown)(2, 1).Select
End If Cheers guys but I get a message saying 'object doesn't support this property or method' error for the selection.specialcell line.... I'm using Excel 97?


Re: selecting first available blank row in column

Posted by Mark W. on February 11, 2002 4:33 PM
Excel97 does support SpecialCells. It's the
VBA equivalent of the Edit | Go To... | Special...
menu command! I used the Macro Recorder to
create the original statement. Perhaps you should post the exact code that is producing the error. Can't think why it's not working unless Excel 97 doesn't support the SpecialCells method (can't remember). You could use this instead if you like :- If Cells(1, 1) = "" Then


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.