Announcement

Collapse
No announcement yet.

Move complete row if condition met

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Move complete row if condition met



    Good Evening and New Year Greetings,
    Sirs,
    I am using excel-2010. I have a table named as "Data" basically it is a master data table. i need a macro which check column D if found "Clear" (value in column D is a result of IF formula) then cut full row and paste it in Sheet named as "Cleared". Every day new data will be added in "Data" sheet, next time when we run macro then macro search again "Clear" value in column D and cut paste rows down the earlier data in sheet "Cleared"

    A B C D E
    01-Dec-14 27-Dec-14 5,073.00 26-Dec-14 Clear
    01-Dec-14 17-Dec-14 13,253.80 16-Dec-14 Pending
    01-Dec-14 02-Dec-14 22,265.00 01-Dec-14 Clear
    01-Dec-14 04-Dec-14 20,812.00 03-Dec-14 Clear
    Thank you very much.

  • #2
    Re: Move complete row if condition met

    Hi Narendra kumar,

    Welcome to the Ozgrid forum.

    Based on your data above, I have assumed that the formula to calculate the value "Clear" is in column E, not D as stated.

    Code:
    Sub MoveCleared()
        Dim ws1 As Worksheet, ws2 As Worksheet
        Dim lRows As Long, iCols As Integer
        Dim rClear As Range, rArea As Range
        
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        
        Set ws1 = ThisWorkbook.Worksheets("Master")
        Set ws2 = ThisWorkbook.Worksheets("Cleared")
        
        With ws1
            With .ListObjects("Data").Range
                lRows = .Rows.Count
                iCols = .Columns.Count
                .AutoFilter Field:=5, Criteria1:="Clear"
            End With
                
            Set rClear = Nothing
            On Error Resume Next
            Set rClear = .Range(.Cells(2, 1), Cells(lRows, iCols)).SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            
            If Not rClear Is Nothing Then
                rClear.Copy ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Offset(1, 0)
                For Each rArea In rClear.Areas
                    rArea.EntireRow.Delete
                Next
            End If
            
            .ListObjects("Data").Range.AutoFilter Field:=5
        End With
        
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        
    End Sub
    Regards,
    Batman.

    Comment


    • #3
      Re: Move complete row if condition met

      Hi Batman Sir,
      Greeting for the day.
      First of all thank you very much for your reply, and sorry for delay from my side.

      Sir when i run code Run Time error-'9'-Subscript out of range appear. and highlight the code line With .ListObjects("Data").Range.
      Please advise what i am doing wrong.

      Comment


      • #4
        Re: Move complete row if condition met

        Hi there,

        What line is the error shown on? The code is looking for a specified item that does not exist.

        Comment


        • #5
          Re: Move complete row if condition met

          Hi Mawhrin,

          The error shown on line (bold one)

          Code:
          With ws1 
                  With .ListObjects("Data").Range

          Some clarification from my side
          1) i have two sheets
          a) "Master"-in this sheet i have data
          b) "Cleared"- cleared sheet whare data to be moved if condition met.

          2)in this code line "Data" means what? is it column header or ???
          With .ListObjects("Data").Range


          Looking for help in this regard.


          Thakns
          Last edited by Narendra kumar; January 18th, 2015, 14:11. Reason: more speciic.

          Comment


          • #6
            Re: Move complete row if condition met

            My code assumes that you have a 'table' object, called 'Data', in a worksheet called 'Master'.

            If what you described as a Table is in fact a named range, or just a range of cells not actually named, then the code will need to be different. Perhaps you could clarify, or upload a sample of your workbook.
            Regards,
            Batman.

            Comment


            • #7
              Re: Move complete row if condition met

              Hi Sir,

              Uploaded workbook. Workbook contain 6 sheets.

              1st- BANK-Processing- on the basis of Q column(Remark) row should be moved to 6th sheetthat is Bank-Cleared.
              2nd- CMS-Processing- on the basis of AA column(Remark) row should be moved to 5th sheetthat is CMS-Cleared.
              3rd- AR-PAY-Processing- on the basis of AD column(Remark) row should be moved to 4th sheetthat is AR-PAY-Cleared.

              New data will be add every day to 1st, 2nd and 3rd sheet. So next time new rows with remark "Clear", should be moved respective sheets, below earlier data.
              Attached Files

              Comment


              • #8


                Re: Move complete row if condition met

                Originally posted by Batman View Post
                My code assumes that you have a 'table' object, called 'Data', in a worksheet called 'Master'.

                If what you described as a Table is in fact a named range, or just a range of cells not actually named, then the code will need to be different. Perhaps you could clarify, or upload a sample of your workbook.

                Sir You are right, mistake from my side.
                Now it doing perfectly. Thanks a lot ,you are amazing.

                Comment

                Working...
                X