Table: How to Restructure (Cut/Paste/Delete) Table rows based on table column values?

[inddon]

Hello There,

I have a Table, which gets populated with formulas based on another Table. After this is done, I would like to restructure (cut/paste at the end of the table/delete rows) the table rows. This would be based on 2 Table column values. It is described in the attached workbook in worksheet 'Finaldata'.


There is a post from me, which relates to the deletion part. This current post has the complete requirement.
http://forum.chandoo.org/threads/how-to-bulk-delete-table-rows-based-on-a-table-column-value.31079/


Could you please advise, how this can be achieved using VBA?

Appreciate your help.


Thanks & regards,
Don

 

[Chihiro]

Here use this code snippet.

tbl3.Range.AutoFilter Field:=2, Criteria1:="="
tbl3.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
tbl3.Range.AutoFilter Field:=2

Basically filters for (Blank) in column2 "Description" and deletes empty rows.
Then clears filter condition from range.

Also add ".DisplayAlerts" toggle in your "With Application" part of the code.

See attached.

Edit: Oh wait, I had only looked at previous post. Let me read through your criteria and update.

 

[Chihiro]

A question. How is Column2 to 4 populated?

 

[inddon]

Hi Chihiro,

In Worksheet Finaldata-Table2 are formula columns:
To keep it simple, all the columns are referenced from Table1

Update:
For ease, I have attached the workbook.



Regards,
Don

 

[Chihiro]

Hmm... so at least the sort operation has to be done after manual entry is completed and should be in separate module from the original (Keep the delete code where it is).

Also, code will need to replace values in Row1 of Column1 before resetting.

Ok, I think I have enough to work on.

 

[inddon]

Hmm... so at least the sort operation has to be done after manual entry is completed and should be in separate module from the original (Keep the delete code where it is).

Also, code will need to replace values in Row1 of Column1 before resetting.

Ok, I think I have enough to work on.

Hi Chihiro,

I have updated my previous post, should have replied it. My aplology.

To keep it simple, all columns are referenced formula columns. Please refer to my previous reply, attached file 'Sample Workbook 4'


Thanks & regards,
Don

 

[Chihiro]

If it's all done via formula. Note that you won't be able to sort on columns.

 

[inddon]

Hi Chihiro,

The delete works good, nice simple technique.


A brief suggestion, see if this is easier:
1. Count all rows whose 'Remarks1 is not null and Remarks2 is null'
-Create these number (above count) of rows after the last row in the table
-Paste these rows
2. Repeat the same for 3rd to 4th display (mentioned below)
3. delete all rows, where description is null

Conditions of filter:
1st display - all rows where Remarks1 & Remarks2 are not null
2nd display - all rows where Remarks1 is not null and Remarks2 is null
3rd display - all rows where Remarks1 is null and Remarks2 is not null
4th display - all rows where Remarks1 is null and Remarks2 is null
5th then delete all rows, where description is null


Would this be easier in VB? Just curious.

Please advise.

Many thanks & regards,
Don

 

[Chihiro]

So is all info contained in Table1 then?

Why not do sort operation via code on Table1 and copy/paste result to Table3?

 

[inddon]

So is all info contained in Table1 then?

Why not do sort operation via code on Table1 and copy/paste result to Table3?

Hi Chihiro,

Thank you for your reply. Yes all info is contained in Table1. Yes, I agree with you.

In the original workbook, in final destination Table, it has complex formulas. So doing it all in VBA would become quite complex.

The workbook is just a sample one.

Regards,
Don

 

[Chihiro]

Can you upload your actual workbook set up here or via private conversation?

Further coding will depend on it.

 

[Chihiro]

Resolved in priv conversation. What I ended up doing was to put info into 3rd table from 2nd and do data transformation at each transfer step (using auto filter). Done in 3 stages.

Code looks like below.

Sub copyValTable()
Dim wstbl2 As Worksheet, wstbl3 As Worksheet
Dim tbl2 As ListObject, tbl3 As ListObject
Dim tbl3Count As Integer

Set wstbl2 = Worksheets("FinalData")
Set tbl2 = wstbl2.ListObjects("Table2")

Set wstbl3 = Worksheets("Sample")
Set tbl3 = wstbl3.ListObjects("Table3")

  With Application
      xlCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
  End With
 
If wstbl2.FilterMode Then wstbl2.ShowAllData

tbl3Count = tbl3.DataBodyRange.Rows.Count

If tbl3Count > 1 Then
    tbl3.DataBodyRange.Offset(1, 0).Resize(tbl3Count - 1, _
    tbl3.DataBodyRange.Columns.Count).Rows.Delete
End If

tbl2.Range.AutoFilter Field:=5, Criteria1:="<>"
tbl2.Range.AutoFilter Field:=10, Criteria1:="<>"
tbl2.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy

With tbl3.DataBodyRange.Cells(1, 1)
    .PasteSpecial xlPasteValuesAndNumberFormats
    .PasteSpecial xlPasteFormats
End With

tbl2.Range.AutoFilter Field:=10
tbl2.Range.AutoFilter Field:=5, Criteria1:="="
tbl2.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy

With tbl3.Parent
    With .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 1)
        .PasteSpecial xlPasteValuesAndNumberFormats
        .PasteSpecial xlPasteFormats
    End With
End With

tbl2.Range.AutoFilter Field:=5
tbl2.Range.AutoFilter Field:=10, Criteria1:="="
tbl2.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy

With tbl3.Parent
    With .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 1)
        .PasteSpecial xlPasteValuesAndNumberFormats
        .PasteSpecial xlPasteFormats
    End With
End With

tbl3.DataBodyRange.Columns(1).Formula = tbl2.DataBodyRange.Columns(1).Formula

wstbl2.ShowAllData

wstbl3.Activate
wstbl3.Cells(2, 1).Select
  With Application
      xlCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = True
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
  End With

End Sub

 

[inddon]

Resolved in priv conversation. What I ended up doing was to put info into 3rd table from 2nd and do data transformation at each transfer step (using auto filter). Done in 3 stages.

Thank you Chihiro.


Regards,
Don