Page 7 of 9 FirstFirst ... 56789 LastLast
Results 61 to 70 of 83

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

  1. #61
    Senior Member
    Join Date
    Jul 2019
    Posts
    382
    Rep Power
    0
    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

  2. #62
    Senior Member
    Join Date
    Jul 2019
    Posts
    382
    Rep Power
    0
    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

  3. #63
    Senior Member
    Join Date
    Jul 2019
    Posts
    382
    Rep Power
    0
    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

  4. #64
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,455
    Rep Power
    10
    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 )
    Code:
     ' 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
    Attached Files Attached Files
    Last edited by DocAElstein; 07-19-2020 at 02:33 PM.
    A Folk, A Forum, A Fuhrer ….

  5. #65
    Senior Member
    Join Date
    Jul 2019
    Posts
    382
    Rep Power
    0
    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

  6. #66
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,455
    Rep Power
    10
    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
    Quote Originally Posted by DocAElstein View Post
    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...
    Last edited by DocAElstein; 07-19-2020 at 07:45 PM.
    A Folk, A Forum, A Fuhrer ….

  7. #67
    Senior Member
    Join Date
    Jul 2019
    Posts
    382
    Rep Power
    0
    Code:
    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

  8. #68
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,455
    Rep Power
    10
    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

    Code:
         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…
    Last edited by DocAElstein; 07-19-2020 at 07:49 PM.
    A Folk, A Forum, A Fuhrer ….

  9. #69
    Senior Member
    Join Date
    Jul 2019
    Posts
    382
    Rep Power
    0
    Problem Solved
    Thnx Alot Doc Sir For helping me in solving this Problem Sir
    Have a Great Day

  10. #70
    Senior Member
    Join Date
    Jul 2019
    Posts
    382
    Rep Power
    0
    Last edited by DocAElstein; 07-26-2020 at 03:18 PM.

Similar Threads

  1. Replies: 29
    Last Post: 06-09-2020, 06:00 PM
  2. Replies: 3
    Last Post: 10-20-2015, 12:51 PM
  3. VBA To Delete Rows Based On Value Criteria In A Column
    By jffryjsphbyn in forum Excel Help
    Replies: 1
    Last Post: 08-15-2013, 12:45 PM
  4. Replies: 6
    Last Post: 08-14-2013, 04:25 PM
  5. Delete Remove Rows By Criteria VBA Excel
    By marreco in forum Excel Help
    Replies: 5
    Last Post: 12-20-2012, 05:56 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •