delete blank rows in table excel word vba

rakesh seebaruth

Active Member
Joined
Oct 6, 2011
Messages
303
Hi Guys

I have the attached table which is bookmarked.

When i execute the macro , the bookmarks are filled from A to E.

I would like vba to delete all blanks rows in the table if cells in sheet are empty.(Let's Cell A is Sun and Cell B to E are empty)

My vba are as follows :-

VBA Code:
With Sheets("security")
        LastRow = .Range("E9999").End(xlUp).Row  'Determine Last Row in Table
        For CustRow = 8 To LastRow
            For CustCol = 5 To 9 'Move Through 9 Columns
                TagName = .Cells(7, CustCol).Value 'Tag Name
                TagValue = .Cells(CustRow, CustCol).Value 'Tag Value
                With objDoc
                    If .Bookmarks.Exists(TagName) Then
                        Set BkMkRng = .Bookmarks(TagName).Range
                        BkMkRng.Text = TagValue
                    End If
                End With
            Next CustCol
        Next CustRow
    End With
    MsgBox "Complete"
End Sub

Thanks
 

Attachments

  • Capture.PNG
    Capture.PNG
    1.7 KB · Views: 18

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Maybe something like this would work. It assumes you would be looking for bookmarks with text less than 2 characters and that table(1) is the table you want. If you need a different criteria for finding the target bookmarks or if there are more tables and 1 isn't the right one, you'll have to change those lines.
VBA Code:
Sub DeleteBookmarks()
    With Sheets("security")
        LastRow = .Range("E9999").End(xlUp).Row  'Determine Last Row in Table
        For CustRow = 8 To LastRow
            For CustCol = 5 To 9 'Move Through 9 Columns
                TagName = .Cells(7, CustCol).Value 'Tag Name
                TagValue = .Cells(CustRow, CustCol).Value 'Tag Value
                With objDoc
                    If .Bookmarks.Exists(TagName) Then
                        Set BkMkRng = .Bookmarks(TagName).Range
                        BkMkRng.Text = TagValue
                    End If
                End With
            Next CustCol
        Next CustRow

        'Delete bookmarks
        For Each bk In objDoc.Bookmarks
            If Len(bk.Range.Text) < 2 Then
                tblRow = bk.Range.Information(wdStartOfRangeRowNumber)
                objDoc.Tables(1).Rows(tblRow).Delete
            End If
        Next
    End With
    MsgBox "Complete"
End Sub
 
Upvote 0
Thanks for your reply

but unfortunately, it's not working. Please refer to new attachment file.

What i want exactly is vba to delete all blank rows in the table.

thanks
 

Attachments

  • Capture.PNG
    Capture.PNG
    19.2 KB · Views: 18
Upvote 0
Can you use the debug mode to step through the bookmark delete part at the end of the code and describe what it is doing? Is it finding the bookmarks successfully? Is tblRow being accurately determined? etc.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top