Pasting data to a table that may or may not have data in it

masplin

Active Member
Joined
May 10, 2010
Messages
411
Hi. I am trying to write the VBA to paste some selected data into a table that may have no data (so top row is blank) or may have several rows of data.

In my code I select the table head and do end down, but in a blank table this finds row 1 which is empty so data needs to be pasted here. If there is already data it needs to offset down one row before pasting. I can't work out what is wrong with the If clause as for blank table rows will be 1 I assume so does nothing and pastes, where as with data it would drop down one row then paste?

VBA Code:
  Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("CombinedData")
    
    
    
    Sheets("Associate Detail").Select
    Range("C5:V5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Combined").Select
    Range("CombinedData[[#Headers],[Associate Name]]").Select
    Selection.End(xlDown).Select
    
   With tbl.DataBodyRange
    If .Rows.Count > 1 Then
    ActiveCell.Offset(1, 0).Select
    End If
    
    ActiveSheet.Paste
    Range("CombinedData[[#Headers],[Associate Name]]").Select
    End With

Thanks for any advice as sure simple to people clever than me

mike
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Maybe you can use:
VBA Code:
   With tbl
    If .ListRows.Count > 0 Then
    ActiveCell.Offset(1, 0).Select
    End If
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Are you sure?
What if the table have one record?
 

masplin

Active Member
Joined
May 10, 2010
Messages
411

ADVERTISEMENT

When you delete the data in the table you always have one blank row. In this case when in was >0 it is pasting into the 2nd row and leaving the top row blank. So seems row count of an empty table is actually always 1 even if now data in that first row.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
I don't meant one blank row but one filled row.
So far I know Listrows.Count in an empty table is 0.
Do you delete the row with the headers also?
 

masplin

Active Member
Joined
May 10, 2010
Messages
411

ADVERTISEMENT

I've deleted al lrows in the table so I have the header row, but this always leaves the 1st row as a blank row for you to start entering data in. It must count this as 1 row as if i use >1 it starts pasting in row2 so i have a blank row between the data and the headers.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,051
Office Version
  1. 2010
Platform
  1. Windows
My approach would be
VBA Code:
  Dim tbl As ListObject
  Dim lr As Long, x As Long
  Dim arr As Variant
  
  ' The table to work with
  With Sheets("Combined")
    Set tbl = .ListObjects("CombinedData")
  End With
  
  With Sheets("Associate Detail")
    ' last row of data to put into table
    lr = .Range("C:V").Cells.Find(WHAT:="*", _
                SEARCHDIRECTION:=xlPrevious, _
                SEARCHORDER:=xlByRows).Row
  ' Array of values to put into the table
    arr = .Range("C5:V" & lr).Value
  End With
  ' Where to put it in the table
  With tbl
    ' Number of existing listrows
    x = .ListRows.Count
    ' For each row to be added to table add a new row
    For i = 1 To UBound(arr, 1)
        .ListRows.Add
    Next i
    ' Write data into table by resizing the first cell of the first row added to the size of the data being added
    ' Then make that equal to the array
    .DataBodyRange(x + 1, 1).Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    ' Position cursor
    .HeaderRowRange.Cells(, .ListColumns("Associate Name").Index).Select
  End With
 

masplin

Active Member
Joined
May 10, 2010
Messages
411
Thanks but this code works fine for my purposes so don't think it needs to be as complex

Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("CombinedData")



Sheets("Associate Detail").Select
Range("C5:V5").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Combined").Select
Range("CombinedData[[#Headers],[Associate Name]]").Select
Selection.End(xlDown).Select


With tbl
If .ListRows.Count > 1 Then
ActiveCell.Offset(1, 0).Select
End If

ActiveSheet.Paste
Range("CombinedData[[#Headers],[Associate Name]]").Select
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,130,210
Messages
5,640,870
Members
417,174
Latest member
diegomuser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top