The TargetCol was not defined. See modified version.

Sub DeleteData_New()
Dim fRow As Long
Dim Sh As Worksheet
For Each Sh In Test.Sheets
With Sh
'If ActiveSheet.Name < sh.Name Then
'Sheets(sh.Name).Select
'End If
Set f = .Cells.Find(What:="Title for Month")
fRow = f.Row
LastRowToDelete = .Cells(fRow, 1).End(xlDown).Row
.Range(.Rows(fRow + 1), .Rows(LastRowToDelete)).Delete
End With
Next
End Sub


"Yossy" wrote:

This is exactly how I use it and I get this error. "Method or data member not
found" and it highlights the ".column". Also I declared a dim statement for
sh as I got error on that too.

Sub DeleteData_New()
Dim fRow As Long
Dim Sh As Sheets
For Each Sh In Test.Sheets
With Sh
'If ActiveSheet.Name < sh.Name Then
'Sheets(sh.Name).Select
'End If
Set f = .Columns(TargetCol).Find(What:="Title for Month")
MsgBox f.Row
fRow = f.Row
LastRowToDelete = .Cells(fRow, TargetCol).End(xlDown).Row
.Range(.Rows(fRow + 1), .Rows(LastRowToDelete)).Delete
End With
Next
End Sub

All hellp totally appeciated. Thanks a big bunch!!

"JLGWhiz" wrote:

Let's try this again, I put the wrong code in the previous post. This is the
one that I would use.

Sub DeleteData()
Dim fRow As Long
For Each Sh In ThisWorkbook.Sheets
With Sh
'If ActiveSheet.Name < sh.Name Then
'Sheets(sh.Name).Select
'End If
Set f = .Cells.Find(What:="Title for Month")
fRow = f.Row
LastRowToDelete = .Cells(fRow, 1).End(xlDown).Row
.Range(.Rows(fRow + 1), .Rows(LastRowToDelete)).Delete
End With
Next
End Sub

"Yossy" wrote:

I still get th same error. Please what am i missing. Helppppp me. Thanks

"JLGWhiz" wrote:

You are getting the error message on fRow = f.Row because you did not Dim fRow.

Add at the top of your code: Dim fRow As Long

"Yossy" wrote:

Anyone know why I am getting this error - "Object Variable or with block
variable not set" Please help... I want to delete rows of data below "Title
of Month" in multiple sheets. Thus where there is "Title of Month in all
sheets, the code should look below the title and delete the data only in the
row beneath the title until the last data. In this case my data are in Column
A.

Sub DeleteData()
TargetCol = "A"
For Each sh In ThisWorkbook.Sheets
If ActiveSheet.Name < sh.Name Then
Sheets(sh.Name).Select
End If
Set f = Columns(TargetCol).Find(What:="Title for Month")
fRow = f.Row
LastRowToDelete = Cells(fRow, TargetCol).End(xlDown).Row
Range(Rows(fRow + 1), Rows(LastRowToDelete)).Delete
Next
End Sub


Thanks a big bunch