CLearcontent or delete rows: Keep proper count of used row

[ValerieT]

Hello
I need to create a one-to-one relationship between a manager and its team. So when a manager has 2 different teams, I must create a "dummy clone" manager. No problem with that, I add new dummy data starting after the last used rows.

But, if I want to re-run the macro, I need first to clean these new rows ( or dummies would be duplicated)

I tried to delete rows, but get lost on when to increment or not "Ici". It was deleting a row every two rows, or deleting OK but in a neverending loop

Then I tried to simply clearcontents but it seems that UsedRange.Rows.Count doesn't care about it and keep taking these rows into consideration

Sub Del_Blanco()
Dim Lignes As Long
Dim Ici
'check 1st count in A1
Lignes = Sheets("Main data").UsedRange.Rows.Count
Range("A1") = Lignes
Ici = 2
While Ici <= Lignes
    Ici = Ici + 1
    If Left(Cells(Ici, 1), 3) = "IPU" Then
        Cells(Ici, 1).EntireRow.ClearContents
    ElseIf WorksheetFunction.CountBlank(Cells(Ici, 1)) = 1 Then
        Cells(Ici, 1).EntireRow.ClearContents
    End If
wend
'Check 2st count in B1
Lignes = Sheets("Main data").UsedRange.Rows.Count
Range("B1") = Lignes
End Sub

In this example (set in column A), I clear the last 3 rows, and after the cleaning I want the usedRange.Rows to be = 2; But in fact, it stays = 5
Text
Text
(Blank)
IPU
IPU

[ValerieT]

I found a solution

Lignes = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

hoping there is no trick behind it...

[SamT]

The trick is that Excel's UsedRange property is very sloppy in what it thinks is a used cell. Only use it when it does not matter if you are selecting some empty cells.