-1

I have a sheet "Missing". This sheet is generally a part of data from my another sheet "data".

I would like to have the content of my missing sheet always wrapped in a table.

I tried the below code, 

   Sub LoopingThroughTable()
         Sheets("Missing").ListObjects.Add(xlSrcRange, Range("$B$1:$D$16"), , xlYes).Name = _
             "Table19"       
         Sheets("Missing").ListObjects("Table19").TableStyle = "TableStyleLight12"
    End Sub

but with this code, I need to always mention the range. As I told earlier, Since it is an data from another sheet, the row length can vary.

so eevrytime I extract the data from my sheet Data to result, I always wanted to draw a table only for the available content.

Can anyone help, how I can do this ?

Jenny
  • 441
  • 2
  • 7
  • 19
  • set the range dynamically with the row count of your other sheet then? – Plagon Aug 25 '17 at 08:51
  • Possible duplicate of [Last not empty cell in row; Excel VBA](https://stackoverflow.com/questions/4872512/last-not-empty-cell-in-row-excel-vba) – David Rushton Aug 25 '17 at 08:51

1 Answers1

1

As long as the data on the "missing" sheet starts at B1 you can assign a variable for the last row and use that in the range. So your code will be something like:

Sub LoopingThroughTable()
    lastrow = Worksheets("Missing").Cells(Rows.Count, "B").End(xlUp).Row
    Sheets("Missing").ListObjects.Add(xlSrcRange, Range("$B$1:$D$" & lastrow), , xlYes).Name = _
         "Table19"       
    Sheets("Missing").ListObjects("Table19").TableStyle = "TableStyleLight12"
End Sub

Im also assuming the current code works.

ShanayL
  • 1,217
  • 2
  • 14
  • 29
  • The code works. But the problem now is, the table is getting overlapped. – Jenny Aug 25 '17 at 13:43
  • and each time I am running my code, It says, table can not be overlapped with another table – Jenny Aug 25 '17 at 13:44
  • 1
    This happens even when you manually type in the range? Also I made a correction on the `lastrow` variable. – ShanayL Aug 25 '17 at 13:48