Solved

upgrading short code that deletes empty rows in excel

Posted on 2008-06-11
4
261 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

757 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

22 Experts available now in Live!

Get 1:1 Help Now