Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-04-2020, 01:23 AM
gavi12 gavi12 is offline A way to delete when 2nd column is empty Windows 10 A way to delete when 2nd column is empty Office 2016
Novice
A way to delete when 2nd column is empty
 
Join Date: Mar 2020
Posts: 4
gavi12 is on a distinguished road
Default A way to delete when 2nd column is empty

Hi



I am hoping someone will be able to help me. I have managed to put the code in which allows me to delete the code if the entire row is empty but would like to modify it if the 2nd column is empty it will delete as well.

I have attached a copy of the table so you can see what I am trying to do and any help would be appreciated as I have tried to manipulate it numerous ways.

Sub DeleteEmptyTablerowsandcolumns()
Application.ScreenUpdating = False
Dim Tbl As Table, cel As Cell, i As Long, n As Long, fEmpty As Boolean
With ActiveDocument
For Each Tbl In .Tables
n = Tbl.Columns.Count
For i = n To 1 Step -1
fEmpty = True
For Each cel In Tbl.Columns(i).Cells
If Len(cel.Range.Text) > 2 Then
fEmpty = False
Exit For
End If
Next cel
If fEmpty = True Then Tbl.Columns(i).Delete
Next i
Next Tbl
End With
With ActiveDocument
For Each Tbl In .Tables
n = Tbl.Rows.Count
For i = n To 1 Step -1
fEmpty = True
For Each cel In Tbl.Rows(i).Cells
If Len(cel.Range.Text) > 2 Then
fEmpty = False
Exit For
End If
Next cel
If fEmpty = True Then Tbl.Rows(i).Delete
Next i
Next Tbl
End With
Set cel = Nothing: Set Tbl = Nothing
Application.ScreenUpdating = True
End Sub
Attached Images
File Type: jpg table example.JPG (37.1 KB, 12 views)
Reply With Quote
  #2  
Old 04-04-2020, 05:42 AM
macropod's Avatar
macropod macropod is offline A way to delete when 2nd column is empty Windows 7 64bit A way to delete when 2nd column is empty Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,339
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Deleting empty table rows is far simpler than what you're using. For example:
Code:
Sub DeleteEmptyRows()
Application.ScreenUpdating = False
Dim Tbl As Table, r As Long
For Each Tbl In ActiveDocument.Tables
  With Tbl
    For r = .Rows.Count To 1 Step -1
      With .Rows(r)
        If Len(.Range.Text) = .Cells.Count * 2 + 2 Then .Delete
      End With
    Next
  End With
Next
Application.ScreenUpdating = True
End Sub
To delete a row if a cell in a specified column (in this case column 2) is blank:
Code:
Sub DeleteEmptyRows()
Application.ScreenUpdating = False
Dim Tbl As Table, r As Long
For Each Tbl In ActiveDocument.Tables
  With Tbl
    For r = .Rows.Count To 1 Step -1
      With .Rows(r)
        If Len(.Cells(2).Range.Text) = 2 Then .Delete
      End With
    Next
  End With
Next
Application.ScreenUpdating = True
End Sub
PS: When posting code, please structure your code properly and use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
A way to delete when 2nd column is empty Why i can not delete empty row in table? jaryszek Word Tables 2 06-28-2019 04:19 AM
Need to delete duplicate of a column B based on column A and keep ColumnB if unique value to columnA enigmaprince OneNote 0 08-23-2017 01:30 PM
A way to delete when 2nd column is empty Delete Empty Table Rows cltay87 Word VBA 4 02-27-2017 04:23 AM
A way to delete when 2nd column is empty Delete row with empty cel in a table vibor Word VBA 9 05-03-2015 05:42 AM
Creating VBA Code to Delete Empty Column in Table Faugs Word VBA 5 08-07-2014 03:29 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 08:17 PM.


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