Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move to next blank cell in column
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move to next blank cell in column
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 --- Regards, Norman "Dominique Feteau" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move to next blank cell in column
Range("A:A").Find(What:="", After:=Activecell)
"Dominique Feteau" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move to next blank cell in column
gocush wrote:
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move to next blank cell in column (none worked)
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 "Dominique Feteau" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move to next blank cell in column (none worked)
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. "Dominique Feteau" wrote: 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 "Dominique Feteau" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move to next blank cell in column (none worked)
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. -- Regards, Tom Ogilvy "Dominique Feteau" wrote in message ... 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 "Dominique Feteau" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need macro to move to first blank cell in column | Excel Discussion (Misc queries) | |||
how to move contents in column up one cell | Excel Worksheet Functions | |||
Remove Civic numbers in Street Address, move to previous blank cell | Excel Worksheet Functions | |||
Move the last entry in a column to a different cell, when the loc. | Excel Worksheet Functions | |||
Move Cell in Column A to Column B when FIND | Excel Programming |