Deleting rows from a Range variable

[mcomp72]

I have a user-defined function with a few parameters -- two of them take a different range of cells from a worksheet (each range has the same number of rows). The UDF will ultimately do some evaluation on the numbers within some columns of the ranges to find the MIN and MAX values.

But before that can happen, I need to split up one of the ranges into three separate range variables, so each of my ranges contains only one column. That much works. After that, I need to cut off the last row from each range. I'm pretty sure that part also works. Next, I need to loop through one of the ranges to look for a specific value -- "LD". If it finds it, I want that same row to be deleted from all four of the ranges.

I decided to see if CoPilot could help me with this. Someone told me it has become pretty good at writing code, so I thought I would give it a shot. Well... it gave me some code that does not work. But, I can't figure out what exactly is wrong. (I modified the code every so slightly to fit my needs. But the relevant part of trying to delete the rows from the Range variables is directly from it.)

Dim Temp_MainPercentCol As Range
Dim Temp_Fringe1PercentCol As Range
Dim Temp_Fringe2PercentCol As Range
Dim MainPercentCol As Range
Dim Fringe1PercentCol As Range
Dim Fringe2PercentCol As Range
Dim LDCol As Range

'NumOfRows is a parameter of the UDF.
'PercentCols is also a parameter of the UDF.
This breaks it up into 3 ranges, so each range has only 1 column.  This part works.
Set Temp_MainPercentCol = PercentCols.Columns(1).Resize(RowSize:=NumOfRows)
Set Temp_Fringe1PercentCol = PercentCols.Columns(2).Resize(RowSize:=NumOfRows)
Set Temp_Fringe2PercentCol = PercentCols.Columns(3).Resize(RowSize:=NumOfRows)

'This will remove the last row from each range because that row is not needed.  It does this with a function that ChatGPT gave me, which I will paste below.  I think this part is working, but am not 100% sure.
Set MainPercentCol = RemoveLastRowFromRange(Temp_MainPercentCol)
Set Fringe1PercentCol = RemoveLastRowFromRange(Temp_Fringe1PercentCol)
Set Fringe2PercentCol = RemoveLastRowFromRange(Temp_Fringe2PercentCol)
Set LDCol = RemoveLastRowFromRange(TheLDCol) 'TheLDCol is another parameter of the UDF.

'This will loop through LDCol.  If it finds a row equal to "LD", it is supposed to delete that row from all four of the ranges.
lastRow = LDCol.Rows.Count

Debug.Print "Number of rows in MainPercentCol before delete: " & MainPercentCol.Rows.Count

For i = lastRow To 1 Step -1

    If LDCol.Cells(i, 1).Value = "LD" Then
    
        LDCol.Cells(i, 1).EntireRow.Delete
        MainPercentCol.Cells(i, 1).EntireRow.Delete
        Fringe1PercentCol.Cells(i, 1).EntireRow.Delete
        Fringe2PercentCol.Cells(i, 1).EntireRow.Delete
        
    End If
    
Next i

Debug.Print "Number of rows in MainPercentCol after delete: " & MainPercentCol.Rows.Count

When I ran the code, the two Debug.Print statements showed the same number of rows. So the rows were not being deleted. And I confirmed that one of the rows in LDCol had "LD" in it. So it should have deleted 1 row from each range. I stepped through the code and saw it execute the 4 .Delete lines, but they didn't seem to do anything. After, the row with "LD" was still in LDCol.

Here is that other function.

Function RemoveLastRowFromRange(rng As Range) As Range

' Check if the range has more than one row
If rng.Rows.Count > 1 Then
    ' Resize the range to exclude the last row
    Set RemoveLastRowFromRange = rng.Resize(rng.Rows.Count - 1)
Else
    ' If the range has only one row, make no change.
    Set RemoveLastRowFromRange = rng
End If

End Function

Does anyone have any idea why this code isn't working? This is my first attempt at deleting rows from range variables, so I'm a bit out of my league here.

 

[StephenCrump]

I think you (and/or AI) have hugely over-complicated your code.

For example:

- It's not clear why you need to split three adjoining columns into three separate range names.
- Rather than removing the last row from each range, you could set up the correct size in the first place, using .Resize(NumOfRows-1)

You're also going to have a problem applying .EntireRow.Delete to three adjoining columns - as the code will delete three rows rather than one row where there is an "LD" match in LDCol.

How big is LDCol, i.e. what is lastRow?

If you're seeing the four .Delete lines executed, yet the two Debug.Prints show the same number of rows, this suggests that the rows being deleted are outside the relevant ranges.

Sub Demo()

    Dim rng As Range
    Set rng = Range("A1:A10")
    Debug.Print "Before delete, no of rows " & rng.Rows.Count
    rng.Cells(20, 1).EntireRow.Delete
    Debug.Print "After delete, no of rows " & rng.Rows.Count
    
End Sub

What is the value of i when the .Delete's occur? How does this compare to MainPercentCol.Rows.Count?

 

[mcomp72]

Hey Stephen. Thanks for your response.

In regards to splitting the adjoining columns, that was lack of experience on my part. I thought to properly use the MIN and MAX functions, each range needed to be only one column. I have not used those functions before.

The reason I have to remove the last row in the ranges is because I send one row too many to the UDF. That is because the user can add a row at the end of the range, and if they do that, the range being sent to the UDF won't automatically update to include the new row. So I am sending it one row beyond that, so it will always include new rows that may have been added.

After I made my initial post, I had the idea of looping through the rows in the ranges and copying the values into collections, excluding the ones where "LD" was present in the LDCol range. It worked, and I was then able to manipulate the collections the way I needed them to get the UDF to output the proper result. Probably not the most elegant solution, but since it's working, I'm going to go with it and focus on the long list of other things I still need to do. But I really appreciate your help!

 

[StephenCrump]

No problem. I'm glad you got your code to work.

 

[Umma123]

1. Instead of using EntireRow.Delete, use a method to reconstruct the range excluding the rows that need to be deleted.
2. After finding the "LD" value in LDCol, you can filter out the respective rows from all the ranges (MainPercentCol, Fringe1PercentCol, etc.) and redefine those ranges.