Find last row based on a value, then clear only specific Columns

[julhs]

I’m trying to “Find” last row based on a value; then clear only specific Columns below it.

I’m using this code to find the specific last row:-
(which it does)

lrow = sht.Range("T:T").Find(What:="Bla Bla ", LookIn:=xlValues, LookAt:=xlWhole).Row

I then want to clear the contents of SPECIFIC columns from THAT row ( +1 ) down.
eg: “Find” row = Row 50, then clear the contents of ONLY Columns X & Y from Row 51 down to end of sheet

I simply can’t marry up the “Find” row and the clearing of specified columns (+1) below it.
Any help is always greatly appreciated

 

[breynolds0431]

Hello. Try Range("X" & lrow + 1 & ":Y" & lrow + 1).ClearContents.

 

[julhs]

VERY bazar.
I am using:-

lrow = sht.Range("T:T").Find(What:="Bla Bla", LookAt:=xlWhole).Row

in several different situations, without any issues.
This is a test run Sub I put in.

Sub ClearRows()
Dim lrow As Long
Dim rng As Range
Dim sht As Range
lrow = sht.Range("T:T").Find(What:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row
Range("ar" & lrow + 1 & ":as" & lrow + 1).ClearContent ‘ x & y substituted with ar:as
End Sub

But it is bugging out on:-

lrow = sht.Range("T:T").Find(What:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row

Which I really don’t understand!!!I, I have half dozen Subs that have same Declared variables and use the same
lrow “Find” and they run WITHOUT any problem.
Any suggestions as to why??

 

[breynolds0431]

VERY bazar.
I am using:-

lrow = sht.Range("T:T").Find(What:="Bla Bla", LookAt:=xlWhole).Row

in several different situations, without any issues.
This is a test run Sub I put in.

Sub ClearRows()
Dim lrow As Long
Dim rng As Range
Dim sht As Range
lrow = sht.Range("T:T").Find(What:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row
Range("ar" & lrow + 1 & ":as" & lrow + 1).ClearContent ‘ x & y substituted with ar:as
End Sub

But it is bugging out on:-

lrow = sht.Range("T:T").Find(What:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row

Which I really don’t understand!!!I, I have half dozen Subs that have same Declared variables and use the same
lrow “Find” and they run WITHOUT any problem.
Any suggestions as to why??

sht is declared as a range, but should be a worksheet. Something like Dim sht as worksheet: Set sht = Activesheet would be the correct variable declaration.

 

[julhs]

ONCE I declared sht as Worksheet and “Set sht= ThisWorkbook.ActiveSheet!!!! (Mistake I make ONLY to often)
It didn’t bug out!!
Unfortunatley as it stands; it is ONLY clearing the 2nd row of the range in AR:AS (so 2nd row below “Find” row but nothing else)

 

[breynolds0431]

Sorry, I misread what you were trying to achieve. You'd want to first find the row beneath the Cash Paid in T and then the true last row in that dataset. Try the below out:

Sub ClearRows()

'sheet declaration
Dim sht As Worksheet: Set sht = ActiveSheet
'finds the row beneath target row with Cash Paid in column T
Dim fRow As Long: fRow = sht.Range("T:T").Find(What:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row + 1
'finds actual last row based on column T
Dim lRow As Long: lRow = sht.Cells(sht.Rows.Count, "T").End(xlUp).Row

sht.Range("AR" & fRow & ":AS" & lRow).ClearContents

End Sub

 

[julhs]

Excellent, thank you.

Just had to slightly alter;

Dim fRow As Long: fRow = sht.Range("T:T").Find(What:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row + 1

To;

Dim fRow As Long: fRow = sht.Range("T:T").Find(What:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row

And;

sht.Range("AR" & fRow & ":AS" & lRow).ClearContents

To;

sht.Range("AR" & fRow & ":AS" & lRow).Clear

(As “.ClearContents” left the “Comments” but “.Clear” took “Comments” as well)