CODEWITHSUNDEEP
arraysvbaexcelif-statement
elbarto
elbarto

Reputation: 211

Deleting an Array of rows and columns

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?

Upvotes: 2

Views: 361

Answers (1)

user4039065
user4039065

Reputation:

It is only affecting a single cell because you start with a range (Range("B2:G2")) and then tell it to only clear one cell (.Cells(i + 1)) of that range (offset 1 row down). Better to start with a single cell and use the Range.Resize property to adjust the number of columns.

Range("B2").Cells(i + 1).RESIZE(1, 6).Clear

Upvotes: 3

Related Questions