Solved

upgrading short code that deletes empty rows in excel

Posted on 2008-06-11
4
270 Views
Last Modified: 2010-04-21
hi,
i have the attached code, that currently deletes every empty row in a word table (also rows with only numbering bullet). i need to upgrade this code so that also if the row cells containes only spaces (" "), it should also be deletd.
appreciate your kind help :)
Sub DelRows()

    Dim tbl As Table

    Dim rw As Row

    

    Set tbl = ActiveDocument.Tables(1)

    For Each rw In tbl.Rows

        If rw.Range.Characters.Count - rw.Range.Cells.Count = 1 Then

            rw.Delete

        End If

    Next rw

End Sub

Open in new window

0
Comment
Question by:Or_A
  • 2
4 Comments
 
LVL 5

Expert Comment

by:danengle
ID: 21763261
I'm not familiar with the syntax for acquiring the actual cell content so am leaving that to you, but the logic should work:


    For Each rw In tbl.Rows

        If rw.Range.Characters.Count - rw.Range.Cells.Count = 1 Then

            rw.Delete

' replace 

        elseif replace(rw.<whatevertoretrievecontent>, " ", "") = "" then

            rw.Delete

        End If

    Next rw

Open in new window

0
 
LVL 5

Expert Comment

by:danengle
ID: 21763295
Actually, upon further inspection, this deletes just empty cells?  based on the sub you posted, it appears it will delete everything.

Also, for the first If, couldn't it just use:
If rw.Range.Characters.Count = 0 then rw.delete

?
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 21764553
Not sure why this is in Zone VBScript. If you really are using VBScript, these procedures need a bit of tweaking

Similar, but slightly different technique.
Sub DelRows2()

    Dim tbl As Table

    Dim rw As Row

    

    Set tbl = ActiveDocument.Tables(1)

    For Each rw In tbl.Rows

        If Len(Replace$(rw.Range.Text, " ", "")) = (rw.Cells.Count + 1) * 2 Then

            rw.Delete

        End If

    Next rw

End Sub

Open in new window

0
 

Author Closing Comment

by:Or_A
ID: 31466297
worked great, thanks!
but i didnt understand your question, what i've written is not in VB?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now