CODEWITHSUNDEEP
vbaexcel
dom176
dom176

Reputation: 187

Delete a row if a string is found

I am writing a piece of code and I want to delete excess empty rows, by the following criteria

I am doing this by the below code, which searches for a value of 0 in a certain cell, and then I want it to look for the word Total in a string and not activate the if statement if both of those conditions are not met

Currently my Instr function is not working so I need to change that somehow.

Sub Clean_HB()

Dim CLEANHBWS As Worksheet
Set CLEANHBWS = Sheets("Hedgebook")
Dim CLEANFormulaWS As Worksheet
Set CLEANFormulaWS = Sheets("Formula_Template")
Dim Cleanrange As Range

CLEANLastHBR = CLEANHBWS.Cells(CLEANHBWS.Rows.Count, "B").End(xlUp).Row
CLEANClastHBC = CLEANHBWS.Cells(3, CLEANHBWS.Columns.Count).End(xlToLeft).Column
CLEANLastFWSR = CLEANFormulaWS.Cells(CLEANFormulaWS.Rows.Count, "B").End(xlUp).Row
CLEANCLASTFWSC = CLEANFormulaWS.Cells(3, CLEANFormulaWS.Columns.Count).End(xlToLeft).Column

CLEANTickercolumn = CLEANHBWS.Cells.Find(What:="Ticker").Column
CLEANDatecolumn = CLEANHBWS.Cells.Find(What:="Date&Time Booked").Column
CLEANLScolumn = CLEANHBWS.Cells.Find(What:="L/S").Column
CLEANLotscolumn = CLEANHBWS.Cells.Find(What:="Lots").Column
CLEANConversioncolumn = CLEANHBWS.Cells.Find(What:="Conversion Cents").Column
CLEANBorrowcolumn = CLEANHBWS.Cells.Find(What:="Borrow (bps)").Column

For Each Cleanrange In CLEANHBWS.Range(Cells(3, CLEANLotscolumn), Cells(CLEANLastHBR, CLEANLotscolumn))
    If Cleanrange.Value = 0 And Cleanrange.Offset(0, -4).Value <> InStr(1, "total") Then
    Cleanrange.Rows.Select
    Cleanrange.EntireRow.Select
    Selection.Delete
    End If
Next

End Sub

EDIT: Picture of workbook, where the Lots column is the column where I want it to check for zero or blanks, and the Date/Time column which also functions as the Total header column

enter image description here

Appreciate any suggestions

Upvotes: 0

Views: 72

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

You are looking for something like the code below, explanations inside the code's comments:

Dim DelRng As Range ' define a range that will store all rows that needs to be deleted 

' need to add CLEANHBWS also before Cells, to qualify with the sheet you want, and not ActiveSheet 
For Each Cleanrange In CLEANHBWS.Range(CLEANHBWS.Cells(3, CLEANLotscolumn), CLEANHBWS.Cells(CLEANLastHBR, CLEANLotscolumn))
    ' check if cell is empty or equals 0 and not "total" row
    If (Cleanrange.Value = 0 Or Trim(Cleanrange.Value) = "") And InStr(Cleanrange.Offset(0, -4).Value, "total") = 0 Then
        If Not DelRng Is Nothing Then
            Set DelRng = Application.Union(DelRng, Cleanrange)
        Else
            Set DelRng = Cleanrange
        End If
    End If
Next
' make sure there is at least 1 cell inside the range >> delete entire range at 1 line
If Not DelRng Is Nothing Then DelRng.EntireRow.Delete

Upvotes: 2

Related Questions