Excellists,
I have a table in sheet1 of workbook as shown below. Ik want to add a Command_button that shows a list active projects (Column = Status = WIP) per person (Column = BC = Naam1 or Naam2 or Naam3 etc.). The result could be a msgbox or a listbox; i don't really care. To start i need to define the number of rows in the table (to then code something like: For Each cell In sht.Range("A2:A" & LastRow). My attempt to determine the number of rows fails. What am i doing wrong?
Tyvm for you thoughts,
Luc
<colgroup><col><col><col span="2"><col><col><col><col span="2"></colgroup><tbody>
</tbody>
I have a table in sheet1 of workbook as shown below. Ik want to add a Command_button that shows a list active projects (Column = Status = WIP) per person (Column = BC = Naam1 or Naam2 or Naam3 etc.). The result could be a msgbox or a listbox; i don't really care. To start i need to define the number of rows in the table (to then code something like: For Each cell In sht.Range("A2:A" & LastRow). My attempt to determine the number of rows fails. What am i doing wrong?
Code:
Sub BClist()
Dim myWorkSheet As Worksheet, myTable As ListObject, countRows As Long
Set myWorkSheet = ActiveWorkbook.Worksheets("Sheet1")
Set myTable = myWorkSheet.ListObjects("Table1")
countRows = myTable.DataBodyRange.Rows.Count
Debug.Print countRows
End Sub
Tyvm for you thoughts,
Luc
A | B | C | D | E | BC | G | H | Status |
1 | 6-1-2014 | Klant1 | € 150.000 | Naam1 | 23-02-17 | WIP | ||
2 | 9-3-2016 | Klant2 | € 100.000 | Naam2 | 24-02-17 | WON | ||
3 | 8-1-2016 | Klant3 | € 100.000 | Naam3 | 13-06-16 | WON | ||
4 | 20-7-2017 | Klant4 | € 150.000 | Naam1 | 27-07-17 | WON | ||
5 | 28-9-2015 | Klant5 | € 1.000.000 | Naam2 | 25-04-17 | WIP | ||
6 | 21-12-2016 | Klant6 | € 500.000 | Naam3 | 10-01-17 | WON | ||
7 | 22-12-2016 | Klant7 | € 250.000 | Naam1 | WIP | |||
8 | 23-12-2016 | Klant8 | € 500.000 | Naam2 | 7-08-17 | LOST | ||
9 | 24-12-2016 | Klant9 | € 250.000 | Naam3 | WIP | |||
10 | 8-8-2014 | Klant10 | € 3.000.000 | naam4 | 12-07-16 | LOST |
<colgroup><col><col><col span="2"><col><col><col><col span="2"></colgroup><tbody>
</tbody>