Resize Table to Last Non-Blank Row

[bs0d]

I'm using a query to dynamically populate a defined table in Excel. I'm trying to re-size the table to the last non-blank row, without success. I'm getting stuck on detecting the non-blank row. Here's what I've tried (two different approaches), both find the last row the table is set to, which includes a bunch of "blank" rows --instead of the last non-blank row:

        LastRow-a = Sheets("DataTab").Cells(Rows.Count, 17).End(xlUp).Row
        LastRow-b = DataTab.ListObjects("notes").DataBodyRange.Rows.Count

Once I have the LastRow, I think this code should do the trick to implement the resize:

Worksheets("tab-name").ListObjects("tablename").resize Worksheets("tab-name").ListObjects("tablename").Range.Resize(LastRow)

Since it's a defined table, maybe there are special existing functions that make this easier? Thanks for any help

 

[Richard U]

From Ron DeBruin's site.

Function FndLast(choice As Long, rng As Range)
'Ron de Bruin, 5 May 2008
' 1 = last row
' 2 = last column
' 3 = last cell

    Dim lrw As Long
    Dim lCol As Long

    Select Case choice

    Case 1:
        On Error Resume Next
        FndLast = rng.find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
        On Error GoTo 0

    Case 2:
        On Error Resume Next
        FndLast = rng.find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

    Case 3:
        On Error Resume Next
        lrw = rng.find(What:="*", _
                       After:=rng.Cells(1), _
                       Lookat:=xlPart, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
        On Error GoTo 0

        On Error Resume Next
        lCol = rng.find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

        On Error Resume Next
        FndLast = rng.Parent.Cells(lrw, lCol).Address(False, False)
        If Err.Number > 0 Then
            FndLast = rng.Cells(1).Address(False, False)
            Err.Clear
        End If
        On Error GoTo 0

    End Select
End Function

 

[bs0d]

This seems too code heavy, and it looks like it may find the same answer (last row of table), and no the last non-blank cell. In a perfect world, there's a 1 maybe 2 line solution.

 

[Domenic]

I think you had the right idea with your second one, which uses the Resize method of the ListObject...

    With Worksheets("tab-name").ListObjects("tablename")
        .Resize .Range(1, 1).CurrentRegion
    End With

Hope this helps!

 

[bs0d]

I think you had the right idea with your second one, which uses the Resize method of the ListObject...

    With Worksheets("tab-name").ListObjects("tablename")
        .Resize .Range(1, 1).CurrentRegion
    End With

Hope this helps!

Domenic - worked like a charm. Thanks

 

[Falko26]

Hey Guys,

Could you tell me where to paste this code? I'm trying to accomplish the same thing just not sure where the code needs to located to make the resizing work.

Thanks,

 

[Peter_SSs]

Could you tell me where to paste this code?

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window (after editing the code so tab name and table name match yours)
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’

Sub Resize_Table()
  With Worksheets("tab-name").ListObjects("tablename")
    .Resize .Range(1, 1).CurrentRegion
  End With
End Sub

A slight word of warning though. If your table happens to include a blank row among the data it will not "re-size the table to the last non-blank row" per the original request.
For example, in the table below, instead of re-sizing the table to go from row 3 to row 10 it would re-size row 3 to to row 6 and leave the other data (rows 8:10) outside of the table.

Falko26.xlsm
	E	F	G	H
1
2
3	Hdr1	Hdr2	Hdr3	Hdr4
4	1	x	x	x
5	2	x	x	x
6	3	x	x	x
7
8	5	x	x	x
9	6	x	x	x
10	7	x	x	x
11
12
13
14
tab-name

If that was a possibility then you could try this instead

Sub Resize_Table_v2()
  With Worksheets("tab-name").ListObjects("tablename")
    .Resize .Range.Resize(.Range.Find(What:="*", SearchDirection:=xlPrevious).Row - .Range.Row + 1)
  End With
End Sub

 

[Falko26]

Hey Peter,

Thanks for the reply this did help! My table will never include any blank rows but that's still good to know.

The Macro you provided did work but I ended up finding another bit of code to paste directly into my existing macro which automates the resizing.

Credit tonyyy

Dim tbl As ListObject
Dim rng As Range

Set tbl = ToSheet.ListObjects(1)
Set rng = ToSheet.Range(tbl.Name & "[#All]").Resize(tbl.Range.Rows.Count + 1, tbl.Range.Columns.Count)
tbl.Resize rng

Thanks Again!

 

[Domenic]

Hey Peter,

Thanks for the reply this did help! My table will never include any blank rows but that's still good to know.

The Macro you provided did work but I ended up finding another bit of code to paste directly into my existing macro which automates the resizing.

Credit tonyyy

Dim tbl As ListObject
Dim rng As Range

Set tbl = ToSheet.ListObjects(1)
Set rng = ToSheet.Range(tbl.Name & "[#All]").Resize(tbl.Range.Rows.Count + 1, tbl.Range.Columns.Count)
tbl.Resize rng

Thanks Again!

If you simply want to add a new row, which is what your macro does, why not simply use . . .

tbl.ListRows.Add

???

 

[Falko26]

If you simply want to add a new row, which is what your macro does, why not simply use . . .

tbl.ListRows.Add

???

The original idea here was trying to automatically copy rows of data between two different tables on two different sheets based on a cell value. However the Code I was using was not pasting the data into the table itself so I needed a way to resize the table after the copy was completed.

Link to original Post.

VBA Code to Transfer Data from one Table to another based on Cell Value

Hey Team, I'm trying to transfer data between two tables on different sheets depending on the value of the Status Column. Credit Snakehips for getting me this close. I have 3 Codes. one for the module then one for each respective sheet I'm copying to. This code works perfectly but I'm...

www.mrexcel.com

Thanks!