Move to next blank cell in column

[Dominique Feteau]

How do I write a line in a macro I'm trying to create.

What I want is for it to start in A1 and basically go to the next blank cell
going down in Column A?

How do i do that?

Niq

 

[Norman Jones]

Hi Dominique

Try:

Sub Tester01()
Dim FirstBlank As Range

If IsEmpty(Range("A1")) Then
Set FirstBlank = Range("A1")
ElseIf IsEmpty(Range("A2")) Then
Set FirstBlank = Range("A2")
Else
Set FirstBlank = Range("A1").End(xlDown)(2)
End If

End Sub

 

[Guest]

Range("A:A").Find(What:="", After:=Activecell)

 

[Alan Beban]

Range("A:A").Find(What:="", After:=Activecell)

That doesn't work if A1 is active and is blank; or if some other cell is
active and doesn't happen to be the cell in Column A just above the
first blank cell in that column.

Range("A:A").Find(What:="", After:=Range("A" & Rows.Count) will work

Alan Beban

 

[Dominique Feteau]

Thanks guys for your time, but none of your solutions worked. Maybe I should
be a little more specific. I have a range of cells that i've copied on one
sheet and want pasted into another sheet.but starting at the first blank
cell in Column A. Here is the code i'm using. Let me know what i'm doing
wrong.

Sub Move()

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Data").Select
Range("A1").Select
Range("A:A").Find(What:="", After:=Activecell) <---this is where i get
the error.
ActiveSheet.Paste
Range("A1").Select
Sheets("CLSEXCEL").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Data").Select
Range("A1").Select

End Sub

 

[Guest]

Hi,
Think there may be two problems. The copy may have been "turned off," when
you ran the process to find the first open cell in the newly selected sheet.
So I think you will have to find that address first, then do your copy. Maybe
the formula for the find is not working, seems there is a syntax error, but
this works, from above:
Dim FirstBlank As Range
If IsEmpty(Range("A1")) Then
Set FirstBlank = Range("A1")
ElseIf IsEmpty(Range("A2")) Then
Set FirstBlank = Range("A2")
Else
Set FirstBlank = Range("A1").End(xlDown)(2)
End If
Hope this helps. Need to remember to use the variable FirstBlank.

 

[Tom Ogilvy]

Sub Move()
Range("A1").CurrentRegion.Copy Destination:= _
worksheets("Data").Cells(rows.count,1).End(xlup)(2)
Application.DisplayAlerts = False
Sheets("CLSEXCEL").Delete
Application.DisplayAlerts = True
Worksheets("Data").Activate
End Sub

Assumes A1 on Data is not blank.