Re: Delete Table Rows (Blank) Macro Question

Hi Jezebel

I am stuck - I don't do much VBA these daya. I wish to just delete the empty
blank rows in the table regardless of wheher cells split or the row was was
inserted with the tab key. It is for users to clean up tables quickly and
not worry about excess rows they insert or paste that are left blank (clean
up the mess macro for tables). I don't do many table macros I usually code
UserForms for info and recall.

If you would be so kind to help me out - much appreciated:

I added (wrongly)

With pCell
.Tables(1).Cell(1, 1).Delete
End If

And it shows an error? On fthe following End If: on compile.


Public Sub DelTableRowsBlank()
Dim pRowEmpty As Boolean
Dim pCurrentRow As Long
Dim atable As Table
Dim pCell As Word.Cell

Set pCell = atable.Cell(1, 1)

Do
'New row
If pCell.RowIndex > pCurrentRow Then

'Was previous row empty?
If pCurrentRow > 0 And pRowEmpty Then
With pCell
.Tables(1).Cell(1, 1).Delete
End If

'Initialize for new row
pCurrentRow = pCell.RowIndex
pRowEmpty = True

End If

'Current cell is not empty?
If Len(pCell.Range) > 2 Then
pRowEmpty = False
End If

'Get next cell
Set pCell = pCell.Next

Loop Until pCell Is Nothing

'Was final row empty?
If pRowEmpty Then
With pCell
.Tables(1).Cell(1, 1).Delete
End If

End Sub

Regar



"Fred" <fred@xxxxxxxxxxxxx> wrote in message
news:ORoKQZAmGHA.4768@xxxxxxxxxxxxxxxxxxxxxxx
Thanks Jezebel

Will give this a whirl.


"Jezebel" <warcrimes@xxxxxxxxxxxxxx> wrote in message
news:%23P410$$lGHA.3352@xxxxxxxxxxxxxxxxxxxxxxx
The problem is that if the table has merged or split cells, the Rows and
Columns collections may not be available, so trying to retrieve the
.Rows.Count or .Columns.Count properties will throw an error, as will
trying to delete the row. You can check the table's .Uniform property to
see whether this will be an issue.

Depending on quite what you're trying to do, you can work around the
problem by iterating the cells of the table and monitoring the .RowIndex
property to see what row you're in --

Dim pRowEmtpy as boolean
Dim pCurrentRow as long
Dim pCell as Word.Cell

Set pCell = aTable.Cell(1,1)
Do

'New row
if pCell.RowIndex > pCurrentRow then

'Was previous row empty?
If pCurrentRow > 0 and pRowEmpty then
... row pCurrentRow was empty
end if

'Initialize for new row
pCurrentRow = pCell.RowIndex
pRowEmpty = TRUE

end if

'Current cell is not empty?
if len(pCell.Range) > 2 then
pRowEmpty = FALSE
end if

'Get next cell
set pCell = pCell.Next

Loop until pCell is nothing

'Was final row empty?
If pRowEmpty then
... row pCurrentRow was empty
end if


"Fred" <fred@xxxxxxxxxxxxx> wrote in message
news:%230UuIb%23lGHA.4712@xxxxxxxxxxxxxxxxxxxxxxx
Hi all,

I have this macro delete table rows blank which does not work on a table
after I make changes to a row such as split cell 2 columns or insert
rows using CTRL ALT I - am I missing something? Is there a macro that
can delete blank rows regardless of how they are inserted.

If you just create a blank table using Insert Table, ie drag mouse
across number of colums rows and type in some rows it is fine and
deletes blank rows. The moment you add rows or split cells on a row it
gives this error: Runtime error 5941 the requested member of the
collecton does not exist (after running). Can I refine it to
acknowledge blank lines inserted using CTRL ALT I and split cells in a
blank row? Is it viable? What am I missing?

Thank you to whomever can assist.

Public Sub DelTableRowsBlank()
Dim flag As Boolean, I As Long, j As Long, atable As Table
Set atable = Selection.Tables(1)
With atable
For I = .rows.Count To 1 Step -1
flag = False
For j = 1 To .Columns.Count
If Len(.Cell(I, j).Range) > 2 Then
flag = True
Exit For
End If
Next j
If flag = False Then
.rows(I).Delete
End If
Next I
End With

End Sub









.

Relevant Pages

  • Re: Delete Table Rows (Blank) Macro Question
    ... deleting the first cell of the table that contains pCell. ... If pCell.RowIndex> pCurrentRow Then ... If pCurrentRow> 0 And pRowEmpty Then ...
    (microsoft.public.word.vba.general)
  • Re: Error Checking Code, Excel 2000 & 2003
    ... Sub ErrorCheckTEST() ... Dim cell as Range, cell1 as Range ... msg= "ERROR Description is empty" ... MsgBox "ERROR Description is empty" ...
    (microsoft.public.excel.programming)
  • Re: Error Checking Code, Excel 2000 & 2003
    ... 'checks each cell in row have data or no data next row if all blank end ... MsgBox "ERROR Description is empty" ... MsgBox "ERROR Type is empty" ...
    (microsoft.public.excel.programming)
  • RE: Looping Through Variables
    ... i have always used Empty. ... In reference to clearing values in one instruction, its not the cell values ... Dim anyListEntry As Range ... Dim sourceWS As Worksheet ...
    (microsoft.public.excel.misc)
  • RE: execute once formula
    ... Dim PRange As Range, PCell As Range ... > I have a spreadsheet that monitors a component failure based on several ... > failure date if the component fails, and leaves the cell blank if the ...
    (microsoft.public.excel.programming)
Quantcast