jeudi 28 janvier 2016

Deleting an Array of rows and columns vba?

I've got some reports which have sub-totals littered inbetween them. I need to delete the sub-totals, which is easy enough to condition on (as is in my IF statement).

However, my following code just does not delete the entire row of subtotals.

Sub clean_up()
'declare variables
Dim i As Integer
Dim noRows As Integer

'count rows
noRows = Range("B2:B387").Rows.Count

'delete row entries that are duplicates..
For i = 1 To noRows
    If Range("B2").Cells(i + 1, 1) = Range("B2").Cells(i, 1) Then
    Range("B2:G2").Cells(i + 1).Clear


    End If
Next i

End Sub

I do not understand why the Range("B2:G2").Cells(i + 1).Clear of my code is incorrect. Using Range("B2").Cells(i + 1).Clear works to delete the first entry in the row, so shouldn't Range("B2:G2").Cells(i + 1).Clear delete the entire row?

Any help is very much appreciated.

Aucun commentaire:

Enregistrer un commentaire