Auto resize (Shrink) table

rlekkala

New Member
Joined
Feb 3, 2009
Messages
49
Say I have 2X3 table. If I added any values in the next column or row the table automatically expands as excel 2007 currently does. What I want is how to shrink if the row or column is removed?
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,848
Responding to an old post, but I had this code to remove blank rows from a table.
Code:
Sub SortAndShrinkTableRows(sWorksheet As String, sTableName As String)
    
    Dim arytableParms As Variant
    Dim lFirstRow As Long
    Dim lDataRowCount As Long
    'Sort table by its 1st column to get empty cells to bottom rows
    'Resize table to contain only rows with data in first column
    
    With Worksheets(sWorksheet).ListObjects(sTableName)
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range(.Range.Columns(1).Address), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortTextAsNumbers
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        'Show no blanks
        .Range.AutoFilter Field:=1, Criteria1:="<>"
        lDataRowCount = Application.WorksheetFunction.Subtotal(3, Range(sTableName).Columns(1))
        .Range.AutoFilter
        
        arytableParms = Split(.Range.Address, ":")
        lFirstRow = Split(arytableParms(0), "$")(2)
        .Resize Range(arytableParms(0) & ":$" & _
            Split(arytableParms(1), "$")(1) & "$" & _
            lFirstRow + lDataRowCount + IIf(lDataRowCount = 0, 1, 0))
    End With

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,923
Messages
5,525,653
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top