Delete rows of data in multiple sheets

[Yossy]

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

 

[Don Guillett]

You end if is in the wrong place. However, you may like this NON selection
better. Notice the DOTS. I also delete all rows from the bottom up myrow+1.
If you don't want that change to. However, blanks could be a problem.
LastRowToDelete = .Cells(myrow, TargetCol).End(xldown).Row

Also, best to fully describe your find.

Sub DeleteData_Don()
TargetCol = "A"
For Each sh In ThisWorkbook.Sheets
If ActiveSheet.Name <> sh.Name Then
With sh
myrow = .Columns(TargetCol).Find(What:="Title for Month", _
After:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

LastRowToDelete = .Cells(Rows.Count, TargetCol).End(xlUp).Row
.Range(.Rows(myrow + 1), .Rows(LastRowToDelete)).Delete
End With
'MsgBox myrow
'MsgBox LastRowToDelete
End If
Next
End Sub

 

[Yossy]

I still get the same error "Object Variable or with block variable not Set"
and fRow=f.Row is highlighted when i click on the debug option. Yes, I want
the code to delete everything under the row where the words "Title for Month"
is found in all sheets in my workbook. I am using Excel 2007.

All help totally appreciated. Thanks

 

[Yossy]

I still get the same error -" Object Variable not set with block variable"

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 in my
workbook, 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.

Thanks

 

[JLGWhiz]

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]

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

 

[Don Guillett]

I fully tested this. You probably have spaces in your "Title of Month"
So, change
myrow = .Columns(TargetCol).Find(What:="Title for Month", _
to this (Notice the * )
myrow = .Columns(TargetCol).Find(What:="*Title for Month*", _
===
If desired, send your workbook to my address below with a snippet of his
msg.

 

[Don Guillett]

If you need to dim your variables and look for spaces, use this

Sub DeleteData_Don()
Dim targetcol As String
Dim sh As Worksheet
Dim myrow As Long
targetcol = "A"
For Each sh In ThisWorkbook.Sheets
If ActiveSheet.Name <> sh.Name Then
With sh
myrow = .Columns(targetcol).Find(What:="*Title for Month*", _
After:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
MsgBox myrow
' LastRowToDelete = .Cells(Rows.Count, TargetCol).End(xlUp).Row
' .Range(.Rows(myrow + 1), .Rows(LastRowToDelete)).Copy 'delete
End With
' 'MsgBox myrow
' 'MsgBox LastRowToDelete
End If
Next
End Sub

 

[JLGWhiz]

His Yossy, Sorry to take so long to get back to you. It looks like the
problem is in the TargetCol = "A". If your "Title for Month" is not in
column A, then the object variable will not Set as a result of your Find
statement. That will leave f = Empty and result in fRow = f.Row throwing the
error message because f.Row cannot be found until f = a valid address. When
I put the Find criteria in column A the code ran as expected. When I moved
it to another column, it threw the error message. The easy way to fix it is
to eliminate the TargetCol variable and just use Sh.Cells(Find...., etc.
Below is the code that worked for me.

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 = .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

 

[JLGWhiz]

You can take the MsgBox lin out. That was for testing only.

 

[JLGWhiz]

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]

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!!

 

[Don Guillett]

Did you ever try this???????
Is your data in col A? As I said before, send your wb to me if desired

Sub DeleteData_Don()
Dim targetcol As String
Dim sh As Worksheet
Dim myrow As Long
targetcol = "A"
For Each sh In ThisWorkbook.Sheets
If ActiveSheet.Name <> sh.Name Then
With sh
myrow = .Columns(targetcol).Find(What:="*Title for Month*", _
After:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
MsgBox myrow
' LastRowToDelete = .Cells(Rows.Count, TargetCol).End(xlUp).Row
' .Range(.Rows(myrow + 1), .Rows(LastRowToDelete)).Copy 'delete
End With
' 'MsgBox myrow
' 'MsgBox LastRowToDelete
End If
Next
End Sub

 

[JLGWhiz]

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