Add an IF Statement

[pioshelby1980com]

I have this code that I managed to cobble together to delete some rows:

Sub RemoveCEOrder()
Dim foundRange As Range

Set foundRange = Cells.Find(What:="CE Order", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)

Rows(6).EntireRow.Delete
Rows(6).EntireRow.Delete
Rows(6).EntireRow.Delete
Rows(30).EntireRow.Delete
Rows(30).EntireRow.Delete
Rows(30).EntireRow.Delete
With Range("B29:P29").Borders(xlEdgeBottom)

.LineStyle = xlContinuous
.Color = RGB(68, 114, 196)
.Weight = xlThick
End With
End Sub

I would like to add a way to search for the string "CE Order" and if it exists then delete the rows but if it doesn't, then don't.

Can anyone help with this?

 

[Micron]

If "CE Order" is not found then foundRange is Nothing, correct? So after you Set the variable:
If Not foundRange Is Nothing Then

Don't forget to close with End If.

 

[pioshelby1980com]

Hi Micron,

That is brilliant. It worked a treat.

 

[Micron]

Glad I could help. If you have a solution it is customary to mark your thread as solved so that it comes off of a list of unsolved questions. At least I think it does

 

[pioshelby1980com]

Hi Micron,

So this is what I have now.

Sub RemoveCEOrder()
Dim foundRange As Range



Set foundRange = Cells.Find(What:="CE Order No.", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)

If Not foundRange Is Nothing Then

Rows(6).EntireRow.Delete
Rows(6).EntireRow.Delete
Rows(6).EntireRow.Delete
Rows(30).EntireRow.Delete
Rows(30).EntireRow.Delete
Rows(30).EntireRow.Delete
With Range("B29:P29").Borders(xlEdgeBottom)

.LineStyle = xlContinuous
.Color = RGB(68, 114, 196)
.Weight = xlThick

End With
End If
End Sub

I have used this code in another routine to copy cells that are relative to the found cell. Can this portion be modified so that instead of deleting specific rows, it deletes rows that are offset from the row where the string "CE Order No." is found?

Sub test()
'make a variable called foundrange that is of type "Range"
Dim foundRange As Range

'set this variable based on what is found: (note we remove the 'activate' here
Set foundRange = Cells.Find(What:="Total amount of all PO's generated", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)

'You can copy now and do whatever you want. Say you want to copy these values to Sheet2!A1 and B1, respectively:
Sheet2.Range("Summary Sheet").Value = foundRange.Offset(0, 2).Value
Sheet2.Range("Summary Sheet").Value = foundRange.Offset(0, 5).Value

End Sub

 

[Micron]

Refer to the desired row by using Offset?
I'm guessing you repeat those delete lines 3x because you want to remove rows 6,7 and 8? Never seen it done like that but I guess it works for you.

What are you using to wrap your posted code, quote tags? Would be nice if you used code tags instead so that indentation is maintained.

 

[pioshelby1980com]

Hi Micron,

My bad about the way the code was posted.

Anyway I used ChatGPT and was able to put this code together.

Sub DeleteRowsInColumnM_ActiveSheet()
    Dim lastRow As Long
    Dim lastRow1 As Long
    Dim i As Long


    ' Find the last row in column M of the active sheet
    lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "M").End(xlUp).Row
    
            ' Find the last row in column D of the active sheet
    lastRow1 = ActiveSheet.Cells(ActiveSheet.Rows.Count, "D").End(xlUp).Row


    ' Loop through each row in column M
    For i = 1 To lastRow
        ' Check if the current cell in column M contains the target text
        If InStr(1, ActiveSheet.Cells(i, 13).Value, "CE Order No.") > 0 Then ' Column M is the 13th column
            ' Delete the current row
            ActiveSheet.Rows(i).Delete
            ActiveSheet.Rows(i).Delete
            ActiveSheet.Rows(i).Delete
            ' Delete the row 3 rows below the current row
            'ActiveSheet.Rows(i + 2).Delete
            ' Since a row is deleted, decrement the loop counter to recheck the current row
            i = i - 1
        End If
    Next i
    


    
        ' Loop through each row in column D
    For i = 1 To lastRow1
        ' Check if the current cell in column D contains the target text
        If InStr(1, ActiveSheet.Cells(i, 4).Value, "Total amount across all CE Orders") > 0 Then ' Column D is the 4th column
            ' Delete the current row
            ActiveSheet.Rows(i).Delete
            ActiveSheet.Rows(i).Delete
            ActiveSheet.Rows(i).Delete


            ActiveSheet.Range(ActiveSheet.Cells(i - 1, 2), ActiveSheet.Cells(i - 1, 16)).Borders(xlEdgeBottom).LineStyle = xlContinuous
            ActiveSheet.Range(ActiveSheet.Cells(i - 1, 2), ActiveSheet.Cells(i - 1, 16)).Borders(xlEdgeBottom).Weight = xlThick
            ActiveSheet.Range(ActiveSheet.Cells(i - 1, 2), ActiveSheet.Cells(i - 1, 16)).Borders(xlEdgeBottom).Color = RGB(68, 114, 196)
            
            ' Since a row is deleted, decrement the loop counter to recheck the current row
            i = i - 1
        End If
    Next i
    End Sub

 

[Micron]

Pretty sure the most common approach when deleting rows is to count down in the loop, not up and subtract from the counter, and not to write a delete line for every row. Imagine doing that for say, 10, 20 or who knows how many rows.

For i = 3 to 1 Step -1
   ActiveSheet.Rows(i).Delete
Next

I guess you have something that works for you anyway, but the trouble with AI stuff like that is that you're learning to do something in a way that is not typically done and for good reasons.

 

[Joe4]

When adding or deleting rows, you always want to loop through the range backwards (as Micron says), or else you might miss rows (because when the rows shift up, you are moving rows that you have not checked before into a range that you have already checked).

Also, you could replace this part if you wanted to delete the row you are checking and the two rows below it.

            ActiveSheet.Rows(i).Delete
            ActiveSheet.Rows(i).Delete
            ActiveSheet.Rows(i).Delete

with this:

    ActiveSheet.Rows(i & ":" & i + 2).Delete

But if you are looping through the rows backwards, where you come to the last of the three rows you want to delete first, and then need to delete the two rows ABOVE that, you would just need to change that line of code to:

    ActiveSheet.Rows(i - 2 & ":" & i).Delete

 

[pioshelby1980com]

Guys,
Thanks for your help. I did as you suggested and it works except that it messes up one formula in a way that the AI bot didn't. Am I missing something?