Solved

upgrading short code that deletes empty rows in excel

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

810 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