asked on

Delete rows based on criteria after data has been imported, and import data into an excel table

Hello. I have two questions please, if I may:
Q1. How to delete excel rows based on criteria in known column after source data import?
Q2. How to import source data into excel table?

I am exporting data from a 'Master' working excel worksheet to a 'Temporary' excel workbook worksheet, and then importing specific department information from the Temporary excel worksheet into the applicable specific 'department' workbook worksheet (35 departments). Note, the department spreadsheets are set up as a table as there are additional columns with formulas (refer Q2.)

I have a master working data file that contains 25 columns of data, and in the 26th column, a key to identify the end applicable specific department.
This worksheet has all my required formulas and product mapping, so that only the clean and required data is ultimately imported in the specific department worksheet (first 14 columns of data).

I have used the code based on the previous question "Excel VBA: Import data from a closed workbook (posted 2018-01-18), and I can successful import and export my required data into my required columns.

Q1. How do I delete the rows in the Source (Temporary) worksheet, based on the data using the key identifier which is located in row 14, after the 'Copy from Source to Target WB' has been completed, and prior to the Source workbook being closed?

Note in version of 'Copy of Source to Target WB', I have used the following:

SourceWS.Activate
For i = Last Row to 2 Step -1
If SourceWS.Cells(i, 14).Value = "GEMCH" Then
Group = SourceWS.Cells(i, 1).Value ...continues
Identifier = SourceWS.Cells(i,14).Value
..
TargetWS.Cells(erow, 1).Value = Group
Target WS.Cells(erow,14).Value = Identifier

I have tried the following to delete the imported data, without success:
SourceWB.Rows(i).Delete
SourceWS.Rows(i,14).Value = 'GEMCH".Delete
If Source WS.Rows (i,14) Value = "GEMCH", Then Delete Entire.Row

The individual department worksheets are in table format, table size approximately 1000 rows. The first 14 columns are populated based upon the above import.
Additional columns further along in the table contain formulas and data validation, which extend to the end of the of table.
When I import the above data, it adds the data outside the table, ie row 1001.
Q2. How can I import the data into the next blank row within the excel table?

Thank you very much for your time and assistance with my questions.

Roy Cox

can you provide a small example file of the data.

This code provides a way to use AutoFilter to delete rows based on the criteria.

Option Explicit

Sub deleteFilteredData()
    Dim rDelete    As Range
    Dim lCalc  As Long
    Dim sDelete As String
     
    With Application
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
 On Error GoTo exit_proc
     
     'Obtain the value that you want to delete
    sDelete = Application.InputBox("Enter date  for deletion")
    If sDelete = Empty Then Exit Sub 'user cancelled
     
     'Sheet with the data, change the name
    With Sheet1
        .AutoFilterMode = False
         
         'Apply the filter, this range of data starts in A1
        .Cells(1, 1).CurrentRegion.AutoFilter Field:=1, Criteria1:=sDelete
         
        With .AutoFilter.Range
            On Error Resume Next
            Set rDelete = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
            .SpecialCells(xlCellTypeVisible)
'            On Error GoTo 0
            If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
        End With
         'Remove the AutoFilter
        .AutoFilterMode = False
    End With
     
exit_proc:
        .ScreenUpdating = True
        .Calculation = lCalc
    End With
     
End Sub

Open in new window

It will need amending to suit your data, but I'll be pleased to add it if you provide the file and look at why you are having difficulties with the import.

Tanya K

ASKER

Hello Roy

Thank you for your response. I have sent you an email with some files.

Kind regards
Tanya

Roy Cox

You should attach the files to the question.

Tanya K

ASKER

Sorry Roy, I did not see the attach file prompt initially.
Please find the three files attached.

Kind regards
Tanya
Roy.docx
Blank-Test.xlsm
Specific.xlsb

Roy Cox

I'll take a look.

I seem to have two source documents, but no destination example. I take it that you currently have a sheet for each department containing a Table.

Your code seems to be searching in the column headed True File Name, is this correct.

Tanya K

ASKER

The file named Blank Test (worksheet TempFile) is the source file.
The file named Specific is the destination file, with two seperate tabs, and yes there will be one sheet per department, each in table format.
I am using the ImportCodeX to identify the unique data that needs to be imported into the applicable table on the destination tab.

Thanks
Tanya

Roy Cox

I wouldn't have individual macros to run but adapt my autofilter code. I'll post back as soon as I can

Roy Cox

Is the source workbook always the same - name and path?

Tanya K

ASKER

Yes it will be.

Roy Cox

I'll amend my code to work with ListObjects for you, I'm quite busy at the moment though.

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tanya K

ASKER

Hi Roy.
I will try your code tonight and be in touch.
Thanks Tanya

Roy Cox

Tanya, did this work?

Tanya K

ASKER

Hi Roy

Sorry, but no, I have been unable to get it to work. In re-reading my question, I probably put it around the wrong way and was not entirely clear of my objective, so I'm not sure if that has contributed to me not being able to work it out.

I need to import data into the Laboratory specific file, and once imported, remove it from the TempFile. So, the best option there would be the cut (not copy) as per line 39, not 37 (if I have understood it correctly)?

I can import data from the TempFile, into a standard excel worksheet in the Laboratory specific file, but just not into an existing table.
When I import into an existing table, it adds the data to a new line below the table, but I need it to paste on the next blank line from column A, as there are formulas set up in other columns further to the right on the data that I need to import.

When I run the code, the TempFile spread sheet opens, but no data comes across.
My file location has been entered into Line 10; and the source sheet into line 17.
I'm searching for my specific laboratory import code from Column 15 (line 26); adding the name of my destination worksheet to line 31; adding my individual criteria code to line 35 (which is contained within column 15).

I appreciate all and any help Roy.

Roy Cox

I'll need to review the examples, but if I understand correctly you have data with empty rows in, this is a really bad idea. A database of any type should not have empty rows between data.

Tanya K

ASKER

Hi Roy. Yes, I can appreciate that.
I've set the worksheets up as a table to enable all the formulas to carry down, especially when people need to manually insert a new row.
I've reduced the size of the tables to about 600 rows.
I'm looking to bring in cleaned up data (~first 15 columns); and then other people complete the other columns as required.
The other columns contain drop down lists, conditional formatting, and formulas.
Thanks Tanya

Roy Cox

Can you attach revised examples

Roy Cox

Still waiting for revised example

Roy Cox

The code that I have provided works with Tables. I'm not sure what else needs doing but adding rows to a table will automatically include any formulas. If Tanya posts back with specific requests then I'll gladly take a look.