#1
|
|||
|
|||
Delete row with empty cel in a table
Hello,
A process is filling a words document (MS Words 2007). A table in this document has 60 row's. Depending on the process there are 1 or all 60 rows filled. Every cell has a bookmark. (3 cells in a row, number, problem, solution) I know how to find the cells and how to select and delete the row. I want delete the empty rows starting at the end. When there is data/content, than stop macro. My problem is: 1 How do I check the cell if its empty? 2 Do I have to repeat the routine 60 times, or is there a other way? This is what I have: Sub DeleteLegeRegel() Selection.GoTo What:=wdGoToBookmark, Name:="Prob60" (CHECK CEL FOR DATA, HOW?) If empty Then Selection.Expand (wdRow) Selection.Cut Selection.GoTo What:=wdGoToBookmark, Name:="Prob59" (CHECK CEL FOR DATA, HOW?) If empty Then Selection.Expand (wdRow) Selection.Cut Selection.GoTo What:=wdGoToBookmark, Name:="Prob58" Else Selection.GoTo What:=wdGoToBookmark, Name:="Prob59" End If End Sub It shout be simple, but I can't find a proper example. Grtz Vincent |
#2
|
||||
|
||||
You haven't made it clear what constitutes empty but if we assume that the bookmark length = 0 means empty then you might do it this way
Code:
Sub Cleanup() Dim x As Integer, sBkmk As String For x = 60 To 1 Step -1 sBkmk = "Prob" & x If ActiveDocument.Bookmarks.Exists(sBkmk) Then If Len(ActiveDocument.Bookmarks(sBkmk).Range.Text) = 0 Then ActiveDocument.Bookmarks(sBkmk).Range.Rows(1).Delete End If End If Next x End Sub
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Here is another way. An empty cell has a text length = 2. A row marker has a text length = 2. So an empty row with three cells has a text length = 8.
Code:
Sub ScratchMacro() 'A basic Word macro coded by Greg Maxey Do If Len(Selection.Tables(1).Rows.Last.Range.Text) = 8 Then Selection.Tables(1).Rows.Last.Delete Else Exit Do End If Loop End Sub |
#4
|
|||
|
|||
Thank you Guessed and gmaxey,
The solution from gmaxey is working ( Whoow!). Great great! Thanks. I Have 2 tables, 1 with 4 and one with 3 coulombs. So the code is: Although this is working OK, I have still 2 problems. 1st. is the make up/cosmetic: The inner horizontal lines of the table are gray, the borders and the vertical lines are black. After the scratch the bottom line is grey. What can I do about it? 2nd. There is another table in my document with 11 coulombs. In coulomb 1 and 7 is always a number present. How can I ignore coulombs 1 and 7? I hope you can help me. It would be very nice. GrtZ ViBoR |
#5
|
|||
|
|||
This should address your two follow on questions:
Code:
Sub ScratchMacro() 'A basic Word macro coded by Greg Maxey Dim lngIndex As Long, lngLength As Long Dim oRow As Row If Selection.Tables(1).Columns.Count = 3 Then Do If Len(Selection.Tables(1).Rows.Last.Range.Text) = 8 Then Selection.Tables(1).Rows.Last.Delete Else Exit Do End If Loop Selection.Tables(1).Rows.Last.Borders(wdBorderBottom).Color = Selection.Tables(1).Rows.First.Borders(wdBorderTop).Color End If If Selection.Tables(1).Columns.Count = 7 Then Do Set oRow = Selection.Tables(1).Rows.Last lngLength = 0 For lngIndex = 2 To 6 lngLength = lngLength + Len(oRow.Cells(lngIndex).Range.Text) Next lngIndex If lngLength = 10 Then oRow.Delete Else Exit Do End If Loop End If Selection.Tables(1).Rows.Last.Borders(wdBorderBottom).Color = Selection.Tables(1).Rows.First.Borders(wdBorderTop).Color End Sub |
#6
|
|||
|
|||
Almost ready....
Hallo Greg,
Thank you for replay. Its getting better but ...some little things: The process is specific for 3 tables. There are many tables in the document, and I don't want to check al the tables. So I changed it a bit and use the bookmarks to find the table. Table 11 and 15 are working OK. But table 9 not. Only the last row will be deleted even if there is data in the row. The row has 11 coulombs, in coulomb 1 and 7 there's data to ignore. Code:
Sub ScratchMacro() 'A basic Word macro coded by Greg Maxey Dim lngIndex As Long, lngLength As Long Dim oRow As Row 'check table 9 Selection.GoTo What:=wdGoToBookmark, Name:="RM1a" Do Set oRow = Selection.Tables(1).Rows.Last lngLength = 0 For lngIndex = 2 To 5 lngLength = lngLength + Len(oRow.Cells(lngIndex).Range.Text) Next lngIndex Selection.Tables(1).Rows.Last.Delete Exit Do Loop Selection.Tables(1).Rows.Last.Borders(wdBorderBottom).Color = Selection.Tables(1).Rows.First.Borders(wdBorderTop).Color 'check table 11 Selection.GoTo What:=wdGoToBookmark, Name:="ItemNr0" Do If Len(Selection.Tables(1).Rows.Last.Range.Text) = 10 Then Selection.Tables(1).Rows.Last.Delete Else Exit Do End If Loop Selection.Tables(1).Rows.Last.Borders(wdBorderBottom).Color = Selection.Tables(1).Rows.First.Borders(wdBorderTop).Color 'Check table 15 Selection.GoTo What:=wdGoToBookmark, Name:="OvopItem1" Do If Len(Selection.Tables(1).Rows.Last.Range.Text) = 8 Then Selection.Tables(1).Rows.Last.Delete Else Exit Do End If Loop Selection.Tables(1).Rows.Last.Borders(wdBorderBottom).Color = Selection.Tables(1).Rows.First.Borders(wdBorderTop).Color End Sub So the data starts in the 3e row. Is it possible for table 11 and 15 to check the 3 row thirst for data in coulomb 3 and 4, if there is no data place a text in second row: 'No problems'. So there is always one row with a message. Table 9 has to be completely delectated when there is no data at al. I hope you would help me again! GrZ ViBoR Holland |
#7
|
|||
|
|||
I'm going to be out the rest of the day. This should resolve the table 9 issue:
Code:
Sub ScratchMacro() 'A basic Word macro coded by Greg Maxey Dim lngIndex As Long, lngLength As Long Dim oRow As Row 'check table 9 Selection.GoTo What:=wdGoToBookmark, Name:="RM1a" Do Set oRow = Selection.Tables(1).Rows.Last lngLength = 0 For lngIndex = 2 To 11 If lngIndex <> 7 Then lngLength = lngLength + Len(oRow.Cells(lngIndex).Range.Text) End If Next lngIndex If lngLength = 18 Then Selection.Tables(1).Rows.Last.Delete Else Exit Do End If Loop Selection.Tables(1).Rows.Last.Borders(wdBorderBottom).Color = Selection.Tables(1).Rows.First.Borders(wdBorderTop).Color End Sub Best Regards, Greg Maxey The dogs bark but the caravan moves on. ~ Persian Proverb |
#8
|
|||
|
|||
...still got some problems.
Halo Greg,
Sorry but this part (table9) is not working. I've checked your code and can't find a fault in it. I tried to change some parameters but it won't work. Do you have an idea to fix it? Table 11 and 15 works fine. I've tried to make it even better to place a text in the first row with a text "No problems". After that the empty rows are deleted. But I can't get the text in the desired cell (Opmerking0). This is watt I tried: Code:
Sub checkEmptyFirstRow() Selection.GoTo What:=wdGoToBookmark, Name:="Opmerking0" If Len(Selection.Tables(1).Rows.Last.Range.Text) = 10 Then ActiveDocument.Bookmarks("Opmerking0").Range.Text="No problems" End If End Sub Above the thirst (data) row there are 2 header rows. Sorry if my English is poor. Grt ViBoR From Holland |
#9
|
|||
|
|||
I don't know how to fix it because I don't know what is not working. You said you have a 11 column table with data always in column 1 and 7. The code I provided you looks at columns 2-6 and 8-11 starting with the last row of the table. If they are all empty then the row is deleted.
The first time a row contains content in 2-6 or 8-11 the execution stops. |
#10
|
|||
|
|||
Hello Greg,
Sorry your code is OK. I forgot a few coulombs with a checkbox. They are count one extra in the form. It doesn't matter or the boxes are on or off. So table 9 is also working fine for deleting empty row's. Thank you again! The under line is also working in table 11 and 15. In table 9 is it working when the whole inner table has a line. In table 9 I want in the middle a blank coulomb because I want a border between the right and left part. (actually it are two tables next to each other). When I do this, the line Selection.Tables(1).Rows.Last.Borders(wdBorderBott om).Color = Selection.Tables(1).Rows.First.Borders(wdBorderTop ).Color isn't working anymore. I tried to fix it to change some codes but ...I'll think I need some help... The test document is in the attachment. Greetz ViBoR. From a rainy Holland Last edited by vibor; 05-03-2015 at 05:43 AM. Reason: writing faulth |
Tags |
empty cell delete row |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro to delete all empty rows from all tables | braddgood | Word VBA | 15 | 10-02-2015 01:54 PM |
Delete empty cell and shift up | Linh | Word Tables | 1 | 09-21-2014 01:52 PM |
Creating VBA Code to Delete Empty Column in Table | Faugs | Word VBA | 5 | 08-07-2014 03:29 PM |
Delete lots of empty space between paragraphs. | FieldTechnician | Word | 4 | 10-25-2013 01:14 PM |
Macro to delete rows with all empty cells | ubns | Excel Programming | 2 | 08-14-2012 02:01 AM |