Pete
12-02-2008, 01:30 AM
see snippet code
Hi Experts
I need to edit the following vba code so that the rows in the tables increase/decrease accordingly to the amount of data that is shown in each table row(s).
If there is not data in a particular table then just show one blank row under the table heading? See worksheet"Latam Santander". If there is data then adjust the table so only the correct number of rows that contain data are visible.
Below is my first attempt but the code is not doing what i planned to do...
Sub correct_table_sizes()
Dim ws As Worksheet
Dim i As Long
Dim flag As Long
For Each ws In Worksheets
flag = 0
With ws
If ws.Name <> "Task_Table1" Then
For i = 65 To 23 Step -1
If .Cells(i, 1).Offset(-5, 0) = "Milestones" And _
.Cells(i, 1).Borders(xlEdgeTop).LineStyle = xlContinuous Then
flag = 1
End If
If flag = 0 And _
.Cells(i, 1).Borders(xlEdgeBottom).LineStyle = xlContinuous Then
.Rows(i).EntireRow.Delete
End If
If flag = 1 And Trim(.Cells(i, 1).Offset(4, 0)) = "TCIP Ref." Then
flag = 0
End If
Next i
End If
End With
Next ws
End Sub
Hi Experts
I need to edit the following vba code so that the rows in the tables increase/decrease accordingly to the amount of data that is shown in each table row(s).
If there is not data in a particular table then just show one blank row under the table heading? See worksheet"Latam Santander". If there is data then adjust the table so only the correct number of rows that contain data are visible.
Below is my first attempt but the code is not doing what i planned to do...
Sub correct_table_sizes()
Dim ws As Worksheet
Dim i As Long
Dim flag As Long
For Each ws In Worksheets
flag = 0
With ws
If ws.Name <> "Task_Table1" Then
For i = 65 To 23 Step -1
If .Cells(i, 1).Offset(-5, 0) = "Milestones" And _
.Cells(i, 1).Borders(xlEdgeTop).LineStyle = xlContinuous Then
flag = 1
End If
If flag = 0 And _
.Cells(i, 1).Borders(xlEdgeBottom).LineStyle = xlContinuous Then
.Rows(i).EntireRow.Delete
End If
If flag = 1 And Trim(.Cells(i, 1).Offset(4, 0)) = "TCIP Ref." Then
flag = 0
End If
Next i
End If
End With
Next ws
End Sub