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

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

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

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

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

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

Posted by Menelaus on February 09, 2002 3:54 PM

Or ....

Columns("A:A").SpecialCells(xlCellTypeBlanks)(1, 1).Select

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

Excellent!!! [nt]

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?

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?

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