AutoFilling down to that last

user_18305410 0
Viewed 32

I have this simple code to apply a 'Table' to a range.

However, it isn't autofilling down to the last value as it should. Can't see what I've done wrong with my 'end down' and 'end right'.

ActiveSheet.ListObjects.Add(xlSrcRange, Range([F22].End(xlDown), [F22].End(xlToRight)), , xlYes).Name = "Table1"
vba excel
1 Answers

Referencing a Range

3.) Your 'Executing' Line of Code

ws.ListObjects.Add(xlSrcRange, rg, , xlYes).Name = "Table1"

Note that the default name of the first table in a workbook is Table1 making it possibly redundant in the line.

1.) The Worksheet

Dim ws As Worksheet
' This maybe safer...
Set ws = ThisWorkbook.Worksheets("Sheet1")
' ... than this:
'Set ws = ActiveSheet

2.) The Range

Dim rg As Range

CurrentRegion

' This will work if the range is in table format i.e. no empty rows or columns.
Set rg = ws.Range("F22").CurrentRegion   

' But it may fail if there is data adjacent to the left or to the top
' when you will want to use:
Dim FirstCell As Range: Set FirstCell = ws.Range("F22")
With FirstCell.CurrentRegion
    Set rg = FirstCell.Resize(.Row + .Rows.Count _
        - FirstCell.Row, .Column + .Columns.Count - FirstCell.Column)
End With

End

Set rg = ws.Range("F22", ws.Cells(ws.Cells(ws.Rows.Count, "F").End(xlUp).Row, _
    ws.Cells(22, ws.Columns.Count).End(xlToLeft).Column))

UsedRange

With ws.UsedRange
    Set rg = ws.Range("F22", .Cells(.Rows.Count, .Columns.Count))
End With

Find

With ws.Cells
    Set rg = ws.Range("F22", ws.cells( _
        .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row, _
        .Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column))
End With
user_98140690
Related