Remove blank cell from bottom of table

  • 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.


  • Re: Remove blank cell from bottom of table


    Hello,


    Just wondering if you need to clear the contents of a given cell ... or delete the last row of your table ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Remove blank cell from bottom of table


    Hi pike, thanks for your reply . When the table is updated the table has a blank cell below the last cell with data which causes a problem when updating powerpivot. So resizing is what I'm looking for. I like the idea of using -1. Thinking about it now I am worried about the possibility of there not being a blank at times and if your suggestion may remove the last row of data in this instance. I will have a go with it when I get home tonight, thanks again

  • Re: Remove blank cell from bottom of table


    My apologies, when I posted on the other site last night I couldn't see it anywhere, not even in the 'my subscribed threads' section. I kind of thought it hadn't been processed so I came here. I posted a couple of replies from my phone at work today and did think about the cross posting thing but you are right I have been around long enough but it is quite rare that I post so I apologise again for being a bit rusty and forgetting a key rule.

  • Re: Remove blank cell from bottom of table


    I have what I want now, its a bit round the houses and requires manual input if there is an error but I'm ok with that...it works fine, thanks for the help and I must remember to post a link if I cross post! sorry.


    [ufCODE]Sub Today()


    Dim newsize 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


    'removes blank cell at bottom of table by resizing table


    newsize = ActiveSheet.Range("P1").Value
    Set rng = Range("TodayQ[#All]").Resize(newsize, 1)
    ActiveSheet.ListObjects("TodayQ").Resize rng


    'creates a helper column to check no values are accidently removed by resizing
    ' I have a cell that tells me true or false


    Columns("T:T").Select
    Selection.ClearContents
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "Slot check"
    Range("T2").Select
    Range("M2:M101").Select
    Selection.Copy
    Range("T2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveSheet.Range("$T$2:$T$101").RemoveDuplicates Columns:=1, Header:=xlNo



    Application.ScreenUpdating = True



    End Sub[/ufCODE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!