Finding the next empty Row always seems to ignore the first row of a table. Even if empty. Please Help.

[PsychoCube]

Hi All!,

My First post here..

I am using the follwing string to return the next empty row in a table.

FindLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1).Row

If the table is empty, with just the single empty row underneath the column headings, the function using the "FindLastRow"...

ws.Range("A" & FindLastRow).PasteSpecial Paste:=xlPasteValues

...pastes the data in the row beneath the single empty table row, and thus pasting the data outside of the table.

In case it is relevant, A1 and A2 are also empty cells, the table starts in A3, the empty table row starts in A4, and the data is always pasted in A5...

Thanks for any advice or support in advance

 

[Micron]

My guess is that if the table is empty the last row is the first empty row, but then you Offset one row for some reason.

 

[dmt32]

Hi

Welcome to forum

You code likely to work well for data in a Range but as you have discovered, can be a problem when working with tables.

Untested but see if update to your code helps you

    Dim tbl           As ListObject
    Dim FindLastRow   As ListRow
    
    'assumes you only have one table in the worksheet
    Set tbl = ws.ListObjects(1)
    
    'Add New row to the table
    Set FindLastRow = tbl.ListRows.Add(AlwaysInsert:=True)
    
   'perform your copy action AFTER you have added the new row to table
   
   'post copied data to table
   FindLastRow.Range(1).PasteSpecial Paste:=xlPasteValues
    
   'clear clipboard
   Application.CutCopyMode = False

Please ensure that you declare the variables with the data types shown.


Hope Helpful


Dave

 

[Alex Blakenburg]

Welcome to the Forum.

The issue is that xlUp finds the last row in the table not the last empty row in the table.
Ideally we would use Table/ListObject referencing but based on your post this should work.

FindLastRow = ws.Columns("A").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1).Row