0

I need to create a macro that selects all cells with data in them and formats as a table style medium 2. I have this code,

Sub A_SelectAllMakeTable()
    Dim tbl As ListObject
    Dim rng As Range

    Set rng = Range(Range("A7"), Range("A7").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.TableStyle = "TableStyleMedium2"
End Sub

But for some reason it picks up cells that don't have data in them and formats then in the table. Do you know why that might be?

BigBen
  • 46,229
  • 7
  • 24
  • 40
Nadezhda Karkelanova
  • 35
  • 1
  • 4
  • 3
    `xlLastCell` is unreliable. See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the true last row. – BigBen Jun 13 '19 at 15:10
  • 2
    `Range("A7").CurrentRegion` if your table has no blank rows/columns and is surrounded by empty cells. – Tim Williams Jun 13 '19 at 15:15
  • If you have data in your first column (usually), and assuming all your headers are populated, just get lastrow/lastcolumn values (various ways to do so), and use that to set your range. – FAB Jun 13 '19 at 15:16

0 Answers0