Trying to select first open or blank cell (working downwards) in column A starting from row 13 which is never blank

[Sackette]

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?

 

[Gary McMaster]

Hi and welcome aboard!

Maybe something like this:

Public Sub Test()

Dim oBlanks As Range

Set oBlanks = Range("A13:A" & ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks)

MsgBox "First Blank: " & oBlanks.Cells(1).Address

End Sub

 

[Sackette]

Thanks for the response, I will give it a try.
Also, proper forum edicate has been pointed out to me... also posted question at:
Trying to select first open or blank cell (working downwards) in column A

 

[Sackette]

Thank you Gary for the suggestion. The sample code you provided did work very well. However I was not able to figure how to incorpoarte it into my macro. As mentioned above, I did cross post this question, not knowing any better at the time, and I was provided with a solution at ExcelGuru.

Thank you for taking the time to respond to my post.