Excel Power Query - check table to see if its blank

[gheyman]

I have VBA code with this line in it.

'Move data from PQ Cost Source
Sheets("PQ Cost Source").ListObjects("PQ_Cost_Source").DataBodyRange.Copy Sheets("Tempory Table CS").Range("A2")'Move data from PQ Cost Source
Sheets("PQ Cost Source").ListObjects("PQ_Cost_Source").DataBodyRange.Copy Sheets("Tempory Table CS").Range("A2")


Is there a way to check the table to see if there is any data before it copies?

Thanks

 

[smozgur]

Two methods:

If Sheets("PQ Cost Source").ListObjects("PQ_Cost_Source").ListRows.Count > 0 Then
    Msgbox "There is data in the table"
End If

If Not Sheets("PQ Cost Source").ListObjects("PQ_Cost_Source").DataBodyRange is Nothing Then
    Msgbox "There is data in the table"
End If

There is a trick here. Say there is no data in the table, but the first row is still a row which is ready for data entry, because you deleted all rows by actuall deleting the rows. However, let's say you had one row with data, but instead deleting the row, you cleared content of the cells. In this case, the first two methods above will not know that the table is empty.

In this case, the following could be more reliable:

If Sheets("PQ Cost Source").ListObjects("PQ_Cost_Source").Range.CurrentRegion.Rows.Count > 1 Then   ' The header row counts
    Msgbox "There is data in the table"
End If

 

[gheyman]

Thank you!

I used:
If Sheets("PQ Cost Source").ListObjects("PQ_Cost_Source").Range.CurrentRegion.Rows.Count > 1 Then ' The header row counts
Msgbox "There is data in the table"
End If

 

[smozgur]

Thank you!

You’re welcome.
Glad to hear it helps.