Results 1 to 8 of 8

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    805
    Articles
    0
    Excel Version
    Excel 2010
    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.

  3. #3
    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.

  4. #4
    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

  5. #5
    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.

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    805
    Articles
    0
    Excel Version
    Excel 2010
    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 by NoS; 2015-04-13 at 11:51 PM.

  7. #7
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    805
    Articles
    0
    Excel Version
    Excel 2010
    The suggestion you got at MrExcel of finding the proper cell to write to works very well.

  8. #8
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •