PDA

View Full Version : [SOLVED] CLearcontent or delete rows: Keep proper count of used row



ValerieT
04-11-2013, 06:11 AM
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
04-11-2013, 08:51 AM
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
04-11-2013, 09:55 AM
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.