Macro to find total and clear contents all rows below total

[howard]

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

 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

 

[Michael M]

How does that code find "total" row
It appears to find the last row only

 

[howard]

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]

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)

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]

Thanks for the help much appreciated

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


Kindly amend your code


See sample data below

Excel 2012
	A	B	C	D	E	F	G	H	I
1	Br1	1
2	Br2	2
3	Br3	2
4	Br4	2
5	Br5	2
6
7
8	Total	9
9
10
11
12	1	1	1	1	1	1	1	1	1
13	1	1	1	1	1	1	1	1	1
14	1	1	1	1	1	1	1	1	1
15
16
Sheet1

 

[sericom]

Try this

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]

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

 

[howard]

Thanks Code works perfectly