PDA

View Full Version : Solved: macro to control rows in tables



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

Bob Phillips
12-02-2008, 01:55 AM
Sub correct_table_sizes()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Dim flag As Boolean

For Each ws In Worksheets
flag = 0
With ws

If ws.Name <> "Task_Table1" Then

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 10
For i = lastrow To 15 Step -1

Debug.Assert i <> 47
If .Cells(i, 1).Borders(xlEdgeTop).LineStyle = xlContinuous Then

flag = True

ElseIf .Cells(i, 1).Value = "Milestones" Then

flag = False
End If

If flag Then

If .Cells(i, "A").Value = "" And _
.Cells(i - 1, "A").Value = "" And _
.Cells(i, 1).Borders(xlEdgeBottom).LineStyle = xlContinuous Then

.Rows(i).Delete
End If
End If
Next i
End If
End With
Next ws

End Sub

Pete
12-02-2008, 02:02 AM
Hi Xld,

ok firstly, need to apologies as i had a positive message from AussieBear regarding my reply. So i have taken his point on board.

Secondly, thanks for the feedback and let me take a look at me mistaken and report back.

thanks

Bob Phillips
12-02-2008, 02:03 AM
I think PM means private message not positive.

Pete
12-02-2008, 02:09 AM
Hi xld

i am getting a error on this line of code Debug.Assert i <> 47

Bob Phillips
12-02-2008, 02:19 AM
No it is not an error, it is a testing break. Just remove that line, it was for my tests.

Pete
12-02-2008, 02:28 AM
Thanks Sir, excellent feeback.

i see me errors