Macro to delete rows

[sagar]

Hello.

Can anyone please help me in writing the macro, I have a work book with all the customer invoices information, that is very big file and we are maintaining the cumulative data . Let me explain the internal process, some time finance team is holding some hours with some specific reason, once it is solved they will bill the remaining hours. Now my question is I want to check the Hours variance column first and delete the repeated “+” and “– “Hours (AH3 cell “+44” hours and AH8 Cell “-44” hours, project ID and Sales month and Customer is same) and the condition is if project id, sales month and customer is matching

E-Column – Project ID
F-Column – Sales Month
R-Column – Customer
AH-Column – Hours Variance

Intention of requesting the macro help is we want to concentrate on not billed cases only, it is very difficult to check manually each entries

As per the attached file macro should delete S.No 1 and 6 to 10 rows

Hope it is clear

Attached the sample data for more clarifications


Regards
Sagar

[Leith Ross]

Hello sagar,

Do you want the entire row deleted or simply cleared? Also, would it be acceptable to have the unique data copied onto another worksheet?

Sincerely,
Leith Ross

[sagar]

Hello Leith Ross,

not a problem to delete the entire row, we are running the macro in Save as file only, master data will be there.

hope it is clear

Regards
Sagar

[Leith Ross]

Hello sagar,

I have added the following macro to the attached workbook. This will show only the unbilled hours.

Sub ShowUnbilledHours()

  Dim Cell As Range
  Dim DSO As Object
  Dim Rng As Range
  
    Application.ScreenUpdating = False
    Set DSO = CreateObject("Scripting.Dictionary")
    
    Set Rng = ActiveSheet.UsedRange.Offset(2, 0)
    Set Rng = Rng.Resize(RowSize:=Rng.Rows.Count - 2)
    
      For Each Cell In Rng.Columns(33).Cells
        If Cell <> "" Then
          If Not DSO.Exists(Abs(Cell)) Then
             DSO.Add Abs(Cell), Cell.Address
          Else
             Range(DSO(Abs(Cell))).EntireRow.ClearContents
             DSO.Remove Abs(Cell)
             Cell.EntireRow.ClearContents
          End If
        End If
      Next Cell
    
      On Error Resume Next
        Set Rng = Rng.SpecialCells(xlCellTypeBlanks)
        If Err = 0 Then
           Rng.Delete
        End If
        
    Application.ScreenUpdating = True
    Set DSO = Nothing
   
End Sub

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

[sagar]

Thanks a lot for your help, perfectly working

Regards
Sagar

[VBA Noob]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save