Delete rows based on match criteria in two excel files or single Excel File

[fixer]

Doc Sir the data can be more than in column U, there is a chance the data will be till column Z or more than that

[fixer]

I am trying to make the last column Dynamic & if I unable share the details then plz look at it tomorrow & help me for the same
Thnx Alot Doc Sir for helping me in solving the same
Have a Awesome Day

[fixer]

I am trying to make the last column Dynamic & if I unable share the details then plz look at it tomorrow & help me for the same
Thnx Alot Doc Sir for helping me in solving the same
Have a Awesome Day

[DocAElstein]

Hi

Making the Last column , Lc, dynamic in your last macro , Sub OnlyHaveRowsWhereColumnCisNotEmpty()

In this case, the usual way we did it before, something like
Ws.Cells.Item(1, Ws.Columns.Count).End(XlToLeft).Column
or like
Ws.Cells.Item(3, Ws.Columns.Count).End(XlToLeft).Column
will not work sometimes
it will not work if , in those examples , row 1 or row 3 was empty, which, based on your test data, might have been the case.
( I am assuming in all this work that rows other than row 1 might have an empty cell in column C, and , like your test data , an entire row might be empty…… )

We can try something else
We can use the UsedRange property's available properties of
the start column, UsedRange.Column
and the columns count UsedRange.Columns.Count
Using
UsedRange.Columns.Count- UsedRange.Column + 1

That is half the solution to the dynamic column problem. It returns us the last column number, which for yout example files , should be 21 ( Column Letter U )

The next part of this problem is that my macro, Sub OnlyHaveRowsWhereColumnCisNotEmpty() , needs to use the column Letter , not the column number.

You need to include this function , ( which you have had this function already from me many times before )

 ' https://excelfox.com/forum/showthread.php/1546-TESTING-Column-Letter-test-Sort-Last-Row?p=7214#post7214
Public Function CL(ByVal lclm As Long) As String '         http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

That function must
Either
Go in the same code module as Sub OnlyHaveRowsWhereColumnCisNotEmpty[color=lightgreyDynamicColumns[/color]()
Or
It can go in any other module: Just to illustrate that point I have uploaded two sample workbooks,
macro1.xlsm has the function and the next macro version in it in the same code module.
Macro2.xlsm has them in separate modules, like this:
MainMacroAndFunctionCodeModules.JPG : https://imgur.com/siBZVuz
MainMacroAndFunctionCodeModules.JPG

---

The two changes for the dynamic column is
_1 a new line
Dim Lc As Long: Let Lc = Ws.UsedRange.Columns.Count - Ws.UsedRange.Column + 1
_2 Modify the column indicia code line, Clms() = Evaluate("=Column(A:U)")
Clms() = Evaluate("=Column(A:" & CL(Lc) & ")")
_3 You need to include the function CL( )

---

The next macro version and function, Sub OnlyHaveRowsWhereColumnCisNotEmptyDynamicColumns() , is here:
https://excelfox.com/forum/showthrea...ll=1#post14663
https://excelfox.com/forum/showthrea...ge29#post14663
and also in the files:
macro1.xlsm : https://app.box.com/s/tl3rs9693jwuv9c2w36ok8fpaewuf0ta
macro2.xlsm : https://app.box.com/s/t35238lm19bj6y0p6m6p68uaknsdf37z







Alan

[fixer]

Thnx Alot Doc Sir for helping me in solving this problem
Problem Solved
Have a Awesome Day Bro

Only a small thing is missing, i want to save the changes also but i am unable to make it
i mentioned wb.save but it is not successful, So i need help regarding the same

[DocAElstein]

What exactly did you try? You must take your time and explain everything more carefully, or you waste my time with lots of unecerssary replies...

---

I want to save the output sir
the macro is runing perfect but in the last we have to mention lines that will save the changes

---

You did not answer my question

What exactly did you try? You must take your time and explain everything more carefully, or you waste my time with lots of unnecessary replies...

[fixer]

Sub STEP8()
Dim arrWbs() As Variant
 Let arrWbs() = Array("C:\Users\WolfieeeStyle\Desktop\A.xlsx", "C:\Users\WolfieeeStyle\Desktop\Files\B.xlsx")

Dim Wb As Workbook, Ws As Worksheet

Dim Stear As Variant
    For Each Stear In arrWbs()
    ' 2a Worksheets data info
     Set Wb = Workbooks.Open(Stear)
                                                                                                                
     Set Ws = Wb.Worksheets.Item(1)
    Dim LrC As Long: Let LrC = Ws.Range("C" & Ws.Rows.Count & "").End(xlUp).Row
    Dim Lc As Long: Let Lc = Ws.UsedRange.Columns.Count - Ws.UsedRange.Column + 1
    Dim arrC() As Variant: Let arrC() = Ws.Range("C1:C" & LrC & "").Value2
    
    Dim Cnt As Long
        For Cnt = 1 To LrC
        Dim strRws As String
            If arrC(Cnt, 1) <> "" Then Let strRws = strRws & Cnt & " "
        Next Cnt
    Let strRws = Left(strRws, Len(strRws) - 1)
    
    Dim Rws() As String: Let Rws() = Split(strRws, " ", -1, vbBinaryCompare)
    Dim RwsT() As Variant: ReDim RwsT(1 To UBound(Rws) + 1, 1 To 1)
        For Cnt = 1 To UBound(Rws) + 1
         Let RwsT(Cnt, 1) = Rws(Cnt - 1)
        Next Cnt
    
    Dim Clms() As Variant
'
     Let Clms() = Evaluate("=Column(A:" & CL(Lc) & ")")
    Dim arrOut() As Variant
     Let arrOut() = Application.Index(Ws.Cells, RwsT(), Clms())
    
     Ws.Cells.ClearContents
     Let Ws.Range("A1").Resize(UBound(arrOut(), 1), 21).Value2 = arrOut()
    
     Let strRws = ""
    Next Stear
    
    arrWbs.Save   'Here we have to mention lineswhich will save the changes done by the macro, I tried by wb.save also &   arrWbs.Save also but it was not correct
    arrWbs.Close   'Here we have to mention lineswhich will close all the files opened by macro
    
    
End Sub
 
 
Public Function CL(ByVal lclm As Long) As String
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

Plz see the code & my remarks
I have mentioned the problem Doc Sir in the remarks

[DocAElstein]

OK, you seem to be trying to save all the files at once. That is not so easy to do.

arrWbs() is just text. Inside it, it just looks like this:
" C:\Users\WolfieeeStyle\Desktop\A.xlsx _ , _ C:\Users\WolfieeeStyle\Desktop\Files\B.xlsx "

You can’t apply any property action command to a piece of text


In my macro, each time the loop is done, the Workbook object, Wb is made from the string path and name of a workbook, ( which is held each time in the variable Stear ).

You know how to save and close a single Workbook with like
Wb.Save
Wb.Close

So you need to have those code lines inside the loop which is done For every workbook

So then Each time it will save Each workbook

     Wb.Save
     Wb.Close
    Next Stear
End Sub

Example in this file:

Macro2.xlsm : https://app.box.com/s/t35238lm19bj6y0p6m6p68uaknsdf37z

---

That’s all I have time for today.
Before you post any questions again, think more carefully , and read what I write…

[fixer]

Problem Solved
Thnx Alot Doc Sir For helping me in solving this Problem Sir
Have a Great Day

[fixer]

http://eileenslounge.com/viewtopic.php?f=30&t=35025