# Macro to find total and clear contents all rows below total

#### howard

##### Well-known Member
I have written code to find "total" in column A and to clear the data below total in Cols A to O

The code only clears one row of data

Your assistance in this regard is most appreciated

Code:
`````` Sub Clear_Data_Below_Total()
finalrow = Range("A65536").End(xlUp).Row
For I = finalrow To 1 Step -1

Cells(finalrow, I).Resize(10, 15).ClearContents
Next I

End Sub``````

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Michael M

##### Well-known Member
How does that code find "total" row
It appears to find the last row only

#### howard

##### Well-known Member
Hi Michael

Total is on Col A after the last data

I don't know how to incorporate this in my code

It would be appreciated if you could help

#### L. Howard

##### Well-known Member
Hi howard

With this formula in A20 =SUM(A1:A19) (nothing below A20 in column A).

And data in cells B2:O40.

Run the code and the data from B21:O30 is deleted.

Does that do it?

Howard (same name, different Howard)

Code:
``````Option Explicit

Sub Clear_Data_Below_Total()
Dim finalrow As Long, i As Long

finalrow = Range("A65536").End(xlUp).Row + 1
For i = finalrow To 1 Step -1

Cells(finalrow, i).Resize(10, 15).ClearContents
Next i

End Sub``````

#### howard

##### Well-known Member
Thanks for the help much appreciated

When running the code, the data below Total is not cleared

See sample data below

Excel 2012
ABCDEFGHI
1Br11
2Br22
3Br32
4Br42
5Br52
6
7
8Total9
9
10
11
12111111111
13111111111
14111111111
15
16
Sheet1

#### sericom

##### Well-known Member
Try this

Code:
``````Sub Clear_Data_Below_Total()
finalRow = Range("A65536").End(xlUp).Row
totalRow = Range("A1:A" & finalRow).Find("Total", LookIn:=xlValues).Row
Range("A" & totalRow + 1 & ":O" & finalRow).ClearContents
End Sub``````

Assumes your last row with data is in col A

#### L. Howard

##### Well-known Member
Maybe this, which is what you started out with I think.

I may not understand your needs.

This

finalrow = Range("A65536").End(xlUp).Row + 1

to

finalrow = Range("A65536").End(xlUp).Row

Howard

Edit, looks like you have data in column A below the TOTAL?

Howard

Last edited:

#### howard

##### Well-known Member
Thanks Code works perfectly

Replies
1
Views
167
Replies
2
Views
376
Replies
6
Views
290
Replies
3
Views
258
Replies
4
Views
527

1,195,650
Messages
6,010,925
Members
441,573
Latest member
Goronvir

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back