Trying to select first open or blank cell (working downwards) in column A

S

Sackette

New member
Joined
Apr 13, 2015
Messages
5
Reaction score
0
Points
0
Hello All
I am new to this forum and VBA codes.
I have the following macro attempting to copy the contents of a range named "blank_line" to the first empty cell in column A (ignoring the first 12 rows) of the active worksheet. I wish to start looking from row 13 which is never blank or empty. The range "blank_line" is located on another sheet of the same workbook. This macro is supposed to find the first empty cell of column A (starting at row 13) of the active sheet regardless of what the active cell is when the macro is started and paste "blank_line" in that location. The macro should end with the cell where "blank_line was pasted as being the new active cell.

Sub AddLine()
'
' AddLine Macro
'
' Keyboard Shortcut: Ctrl+a
'
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
ActiveWorkbook.Names.Add Name:="marker", RefersToR1C1:="=" & ActiveSheet.Name & "!R" & LastRow & "C1"
ActiveWorkbook.Names("marker").Comment = ""
Application.Goto Reference:="blank_line"
Selection.Copy
Application.Goto Reference:="marker"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Names("marker").Delete
End Sub

The above macro works fine until I insert a new row in the middle of the data in which case I need the macro to copy "blank_line" to the new empty row. The macro does not find this new row it simply goes to the first empty row at the bottom of the whole data field. I assume that is because it is working from the bottom of the sheet and looking up to the first blank row.

How can I get it to "look down" starting at A13?

Also, when I first started trying this I thought to name the cell where "blank_line" was to be copied to as "marker" then at the end of the macro deleting the name "marker". I'm realizing that this step is not likely required?​
 
try
Code: 
Dim FirstBlankInA as Long
FirstBlankInA = ActiveSheet.Range("A12").End(xlDown).Row + 1

Before you get to "into" the Excel forums have a look at this
As it is pretty much applicable to all the forums, it could save you a lot of grief with future questions.
 
Thanks for the response, I will give it a try.
Also, thanks for the advise. I'm new to forums and will post apropriately in the future... also posted question at Mr excel.
 
My ignorance in writing code is showing I am sure as I am unable to use your suggestion correctly in my macro. I replaced the first two lines with your code and also in the third line I replaced "LastRow" with "FirstBlankInA". Now the macro continuously writes over whatever is in row 14 rather than the first available blank row. Below is the changes I made to the macro...

Where am I going wrong?
I appreciate the help and patience.


Dim FirstBlankInA as Long
FirstBlankInA = ActiveSheet.Range("A12").End(xlDown).Row + 1
ActiveWorkbook.Names.Add Name:="marker", RefersToR1C1:="=" & ActiveSheet.Name & "!R" & FirstBlankInA & "C1"
ActiveWorkbook.Names("marker").Comment = ""
Application.Goto Reference:="blank_line"
Selection.Copy
Application.Goto Reference:="marker"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Names("marker").Delete
End Sub
 
Still playing with this. In the 2nd line I changed ("A12") to ("A13") and it works perfect! (on Sheet1 at least). Think the problem was the fact that A12 was a blank cell and messing with what we were trying to do.

So, thank you for that. Much appreciated.

However for some reason I have a new problem. This workbook contains multiple sheets and I need the macro to work the same regardless of which sheet is active. That is, whichever sheet is active is were the "blank-line" needs to be copied to. With my original macro way back at the top, that part did work correctly. I cannot see why it does not now. If I run the macro on any sheet other than Sheet1 I get a "Run time error '1004': Method 'Range' of object'_Global failed".

I simplified the copy/paste part of the macro so what I currently have is...

Dim FirstBlankInA As Long
FirstBlankInA = ActiveSheet.Range("A13").End(xlDown).Row + 1
ActiveWorkbook.Names.Add Name:="marker", RefersToR1C1:="=" & ActiveSheet.Name & "!R" & FirstBlankInA & "C1"
Range("blank_line").Copy Range("marker")
Application.Goto Reference:="marker"
ActiveWorkbook.Names("marker").Delete

The problem I am having now for multiple sheets existed both before and after simplifying the copy/paste business so I dont think that created my probelm.

Any help would be appreciated.
 
Hello Sackette,
It's not the way the next blank row is being found that is the problem, it's the way whatever needs to be written into that A cell.

I set up a named range on the same sheet, I only have one sheet in the workbook, and can write it into the next blank cell in column A with this code in a standard module
Code: 
Sub Testing()
    Dim FirstBlankInA As Long
    Dim i As Integer
    
FirstBlankInA = ActiveSheet.Range("A12").End(xlDown).Row + 1
'MsgBox "Row " & FirstBlankInA & " is the row that will be used."
i = Application.WorksheetFunction.CountA((Range("blank_line")))
ActiveSheet.Range("A" & FirstBlankInA).Resize(1, i).Value = Range("blank_line").Value

End Sub
Don't know if this is a good way of doing it or not but it works for me.

Perhaps you should post a sample workbook. Be sure not to include personal data.

Yes using A12 is a problem if it's empty, but using A13 will also be a problem if everything is blank below there.
 
Last edited:
The suggestion you got at MrExcel of finding the proper cell to write to works very well.
 
Thanks for all your help.
My problem with the other sheets in my workbook is exactly as you said above... because this was a new "blank" sheet that I had not be playing around with, all the cells below 13 where blank. I made a simply a change to my sheet so that I am still using A13 as the starting point but have made row 14 a permanent "non-usable" row with some text in there. This was simplier for me then putting something in A12. The result is that the new macro works perfectly on all sheets.

The suggestion I got from MrExcel worked very well when I tried the provided sample however I was not able to figure how to incorporate it into my macro. Again my inexperience showing through.

Again, thank you for your time and assistance.
 
You must log in or register to reply here.
Back
Top