I have a set of tables that are linked to powerpivot. The one table I'm having trouble with either increases or decreases in size each day and only has one column. Because of the daily changes in size it leaves a blank cell at the bottom of the table causing powerpivot to throw an error at me when I try to update it.
I've tried a few different ways and the last one below is the one where I think I'm heading in the right direction but as a novice I'm getting very bogged down with it and would really appreciate some help if its possibble.
Code
Sub Today()
Dim lastrow As Long
Dim rng As Range
Dim tbl As ListObject
Set tbl = ThisWorkbook.Sheets("Tables").ListObjects("TodayQ")
Application.ScreenUpdating = False
'clears previous days table
Sheets("Tables").Select
If tbl.ListRows.Count >= 1 Then
tbl.DataBodyRange.Delete
End If
'collects key numbers from daily list (can contain duplicates)
'places in new sheet and removes duplicates
Sheets("Qualifiers").Select
Range("L2:L101").Select
Selection.Copy
Sheets("Tables").Select
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("R2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range("TodayQ[#All]").RemoveDuplicates Columns:=1, Header:= _
xlYes
'attempts to remove blank cell at bottom of table by resizing table
lastrow = ActiveSheet.ListObjects("TodayQ").Range.Rows.Count
Set rng = Range("TodayQ[#All]").Resize(lastrow, 1)
ActiveSheet.ListObjects("TodayQ").Resize rng
Application.ScreenUpdating = True
End Sub
Display More