Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-11-2012, 10:32 AM
elky1967 elky1967 is offline Delete all rows but the last. Windows XP Delete all rows but the last. Office 2003
Novice
Delete all rows but the last.
 
Join Date: Sep 2012
Posts: 8
elky1967 is on a distinguished road
Default Delete all rows but the last.

Hi all,
First of, I'm not a VB/VBA programmer, I'm a sas programmer, so please forgive me if say something stupid.

I wrote a simple macro in Word 2003 to remove all tables from a document.



Code:
Sub Deltables()
Application.ScreenUpdating = False
 
Dim Tbl As Table
 
With ActiveDocument
  For Each Tbl In .Tables
    Tbl.delete
  Next Tbl
End With
 
Application.ScreenUpdating = True
End Sub
What i really need now is to delete every row of a table except the last one.
Since the tables have different numbers of rows is there a way to do something like...

If not tbl.last.row then delete

Thanks in advance..

Last edited by macropod; 09-11-2012 at 04:26 PM. Reason: Added code tags & formatting
Reply With Quote
  #2  
Old 09-11-2012, 04:30 PM
macropod's Avatar
macropod macropod is offline Delete all rows but the last. Windows 7 64bit Delete all rows but the last. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

In that case, try:
Code:
Sub DelTableRows()
Application.ScreenUpdating = False
Dim Tbl As Table, i As Long
With ActiveDocument
  For Each Tbl In .Tables
    For i = (Tbl.Rows.Count - 1) To 1 Step -1
      Tbl.Rows(i).Delete
    Next
  Next Tbl
End With
Application.ScreenUpdating = True
End Sub
PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 09-13-2012, 07:52 AM
elky1967 elky1967 is offline Delete all rows but the last. Windows XP Delete all rows but the last. Office 2003
Novice
Delete all rows but the last.
 
Join Date: Sep 2012
Posts: 8
elky1967 is on a distinguished road
Default

Thank you for your help macropod. That work perfectly.

What would be the cleanest approach to copy the content of the last row and paste it into the body of the doc?

Thanks again.
Reply With Quote
  #4  
Old 09-13-2012, 03:17 PM
macropod's Avatar
macropod macropod is offline Delete all rows but the last. Windows 7 64bit Delete all rows but the last. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
What would be the cleanest approach to copy the content of the last row and paste it into the body of the doc?
That depends on how many cells there are on that row - if there's only one cell, it's a lot simpler than if there are multiple cells. Of course, if you're just wanting to convert that row to text and end up with no tables in the document, an entirely different approoch might be taken.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 09-13-2012, 06:53 PM
elky1967 elky1967 is offline Delete all rows but the last. Windows XP Delete all rows but the last. Office 2003
Novice
Delete all rows but the last.
 
Join Date: Sep 2012
Posts: 8
elky1967 is on a distinguished road
Default

Hi Macropod,

I actually don't need any tables in my docs. I only need the text in the last row since that is where the statistician puts the footnotes. What approach were you thinking of?
Reply With Quote
  #6  
Old 09-13-2012, 07:25 PM
macropod's Avatar
macropod macropod is offline Delete all rows but the last. Windows 7 64bit Delete all rows but the last. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

For example:
Code:
Sub ExtractTableNotes()
Application.ScreenUpdating = False
Dim i As Long
With ActiveDocument
  For i = .Tables.Count To 1 Step -1
    With .Tables(i)
      .Split .Rows.Count
      .Delete
    End With
    With .Tables(i)
      .Range.Characters.First.Previous.Delete
      .ConvertToText
    End With
  Next
End With
Application.ScreenUpdating = True
End Sub
Note that, simply omitting the .ConvertToText method results in the same outcome as deleting all the previous rows, but probably much more quickly where long tables are involved.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 09-14-2012, 05:38 AM
elky1967 elky1967 is offline Delete all rows but the last. Windows XP Delete all rows but the last. Office 2003
Novice
Delete all rows but the last.
 
Join Date: Sep 2012
Posts: 8
elky1967 is on a distinguished road
Default

That didn't seem to work. I get a Runtime Error: 'Object has been deleted' at this location:

Code:
.Split .Rows.Count
Any ideas?
Thank again.
Reply With Quote
  #8  
Old 09-14-2012, 03:59 PM
macropod's Avatar
macropod macropod is offline Delete all rows but the last. Windows 7 64bit Delete all rows but the last. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

I am unable to replicate that error. If you can provide some more info on the table the code falls over on (especially by including a copy of it in a document attached to a post), I might be able to do something.

Here's a minor enhancement to the code in case it encounters a one-row table:
Code:
Sub ExtractTableNotes()
Application.ScreenUpdating = False
Dim i As Long
With ActiveDocument
  For i = .Tables.Count To 1 Step -1
    With .Tables(i)
      If .Rows.Count > 1 Then
        .Split .Rows.Count
        .Delete
      End If
    End With
    With .Tables(i)
      .Range.Characters.First.Previous.Delete
      .ConvertToText
    End With
  Next
End With
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 09-17-2012, 10:32 AM
elky1967 elky1967 is offline Delete all rows but the last. Windows XP Delete all rows but the last. Office 2003
Novice
Delete all rows but the last.
 
Join Date: Sep 2012
Posts: 8
elky1967 is on a distinguished road
Default

Hi Macropod,

It is failing on tables that have a vertical merge. I didn't notice this before because the first few tables don't have a merged colunm or row. Then I noticed that the macro starts with the last table in the document, which does have a merged column.

Does that help?

Thanks again for your time.
Reply With Quote
  #10  
Old 09-17-2012, 09:00 PM
macropod's Avatar
macropod macropod is offline Delete all rows but the last. Windows 7 64bit Delete all rows but the last. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Working with merged cells does make it difficult to process tables on a row-by-row basis. Try the following macro, which assumes the desired content is always in the table's last cell:
Code:
Sub ExtractTableNotes()
Application.ScreenUpdating = False
Dim i As Long, Rng As Range
With ActiveDocument
  For i = .Tables.Count To 1 Step -1
    Set Rng = .Tables(i).Range.Cells(.Tables(i).Range.Cells.Count).Range
    Rng.End = Rng.End - 1
    Rng.InsertAfter vbCr
    Rng.Copy
    Set Rng = .Tables(i).Range
    Rng.Tables(1).Delete
    Rng.Paste
  Next
End With
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 09-18-2012, 07:40 AM
elky1967 elky1967 is offline Delete all rows but the last. Windows XP Delete all rows but the last. Office 2003
Novice
Delete all rows but the last.
 
Join Date: Sep 2012
Posts: 8
elky1967 is on a distinguished road
Default

Macropod,
That works perfectly. Thank you for all your help.
I was trying to understand what the code was doing so I could modify it but i don't understand how it is setting the focus to the last row.

Is that what this is doing?

Code:
Rng.End = Rng.End - 1
Is it possible to modify the code within the .tables loop to check the contents of the first row and keep it if it equals a string? I thought it would work as long as i add the code before the....

Code:
Rng.Tables(1).Delete
I assume I can using something like:
Code:
if Tbl.Rows(1).??? = 'Listings' then
??.copy
??.paste
end if
But i can't find the correct commands.
Any ideas?
Thanks again.
Reply With Quote
  #12  
Old 09-18-2012, 02:38 PM
macropod's Avatar
macropod macropod is offline Delete all rows but the last. Windows 7 64bit Delete all rows but the last. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The 'Rng.End = Rng.End - 1' is used because, when you select a cell range, you get the end-of-cell marker, which you don't want when you're going to paste the content somewhere else. What the code does instead, is to insert a temporary paragraph break before that, so as to keep the cell's paragraph formatting for whatever is the cell's last paragraph, then copy everything except the end-of-cell marker.

Regarding the string, are you wanting to keep the table, or just the string? Is it a particular string? How many cells are on the first row?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #13  
Old 09-18-2012, 03:16 PM
elky1967 elky1967 is offline Delete all rows but the last. Windows XP Delete all rows but the last. Office 2003
Novice
Delete all rows but the last.
 
Join Date: Sep 2012
Posts: 8
elky1967 is on a distinguished road
Default

Ok, thanks for the explanation.

The tables I want the first row from have one horizatially merged cell. I would need to check if the contents of the cell contains the word 'Listing.' If it does, the contents of the cell needs to be converted to text just like the last row. I have no need for the tables themselves.

Does that help?
Thanks again.
Reply With Quote
  #14  
Old 09-18-2012, 04:32 PM
macropod's Avatar
macropod macropod is offline Delete all rows but the last. Windows 7 64bit Delete all rows but the last. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Try the following. As you can see, there's a lot more involved now.
Code:
Sub ExtractTableNotes()
Application.ScreenUpdating = False
Dim i As Long, Rng As Range
With ActiveDocument
  For i = .Tables.Count To 1 Step -1
    If i = 1 Then
      If .Tables(i).Range.Start = .Range.Start Then
        Set Rng = .Tables(i).Range
        With Rng
          .Tables(i).Range.Cut
          .InsertBefore vbCr
          .Collapse wdCollapseEnd
          .Paste
        End With
      End If
    End If
    Set Rng = .Tables(i).Range.Cells(1).Range
    If InStr(Rng.Text, "Listing") > 0 Then
      With Rng
        .End = .End - 1
        .InsertAfter vbCr
        .Copy
        .Start = .Start - 1
        .Collapse wdCollapseStart
        .InsertAfter vbCr
        .Collapse wdCollapseEnd
        .Paste
        .Characters.Last.Delete
      End With
    End If
    Set Rng = .Tables(i).Range.Cells(.Tables(i).Range.Cells.Count).Range
    With Rng
      .End = .End - 1
      .InsertAfter vbCr
      .Copy
    End With
    Set Rng = .Tables(i).Range
    Rng.Tables(1).Delete
    Rng.Paste
  Next
End With
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #15  
Old 09-21-2012, 05:27 AM
elky1967 elky1967 is offline Delete all rows but the last. Windows XP Delete all rows but the last. Office 2003
Novice
Delete all rows but the last.
 
Join Date: Sep 2012
Posts: 8
elky1967 is on a distinguished road
Default

This is working perfectly.
Thank you for all your help.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete all rows but the last. Macro to delete all empty rows from all tables braddgood Word VBA 15 10-02-2015 01:54 PM
Delete all rows but the last. Macro to conditionally delete rows Steve_D Excel 2 08-24-2012 09:37 PM
Delete all rows but the last. Macro to delete rows with all empty cells ubns Excel Programming 2 08-14-2012 02:01 AM
Delete all rows but the last. merging rows and creating sub-rows gib65 Excel 2 12-09-2011 02:09 PM
delete email message via blackberry and have it delete on my pop3 and my outlook Iamthestorm Outlook 2 10-28-2010 12:21 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:26 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft