Next Blank Cell - Table

[karldugan]

Hi all,

I'm copying some data over from an input spreadsheet into a data table, I'm using the below code which finds the first blank cell in the tables first column and correctly updates the value, but then when I repeat it moves down a row, I assume because the table auto creates the whole row, even though its blank.

Is there a way I can look in the table to find the last blank cell?

The table I am updating is below and the three lines should all be on the same row.

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(lastrow + 1, "A").Select

Value	Requester	Requested Date
1000​
	kdugan
		5/10/2024​

 

[Vogateer]

Yes, you can simulate what the script is doing by using Ctrl+[arrow key], and it will stop at the row just below the table, so it will always move the next row.

I think the easiest way to avoid this issue would be to copy all the values you need then paste them all at once. Those Copy and Paste operations tend to slow the script down, so doing them fewer times could also speed up the script. Are these values in the same order on the source sheet, or are they separated or in a different order?

 

[Vogateer]

If you don't care about efficiency and just want a quick and dirty solution, I think this will do it:

Dim MyTable As ListObject
Dim pasterow As Long
    Set MyTable = Sheets(1).ListObjects(1) 'Adjust if your table is not on the first sheet
    With MyTable
        ' If last column of the last row is blank, do not add to lastrow
        If .Range(.Range.Rows.Count, .Range.Columns.Count).Value2 = "" Then
            pasterow = Cells(Rows.Count, "A").End(xlUp).row
        Else
            pasterow = Cells(Rows.Count, "A").End(xlUp).row + 1
        End If
    End With
   
    Cells(pasterow, "A").Select

 

[Alex Blakenburg]

You really need to show more of your code both of us are only guessing.
It looks like you have multiple issues.
1) Either you are incrementing the lastrow offset each time you are writing to the table or you are repeatedly getting the lastrow
2) xlup is going to find the last row of the table not the last data row in the table.
3) certain table logic will behave differently depending on whether you are starting with an empty table or not and whether the empty table has had its databodyrange deleted.
4) it would have been useful to get the sheet name & table name and how you are moving the data across. I am assuming you are only copying the values.

See if this helps
PS: it does rely on the table default setting of auto expanding is left turned on.

Sub AddDataToTable()

    Dim lastDataCell As Range
    Dim tbl As ListObject
    
    Set tbl = Range("Table1").ListObject                        ' <--- Change to your table name
    Set lastDataCell = tbl.ListColumns(1).Range.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
    ' Assuming you are copying data from 3 separate cells
    ' If they are one contiguous range you would do it as a block in one line
    With lastDataCell
        .Offset(1, 0).Value = Worksheets(2).Range("A10").Value     ' <--- Change source as required
        .Offset(1, 1).Value = Worksheets(2).Range("C10").Value  ' <--- Change source as required
        .Offset(1, 2).Value = Worksheets(2).Range("E10").Value  ' <--- Change source as required
    End With

End Sub