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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Yossy" <(E-Mail Removed)> wrote in message
news:78A4F63A-97FD-4D00-80FA-(E-Mail Removed)...
> 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
>

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

"Breakfast Guy" wrote:

>
> Your code is set to delete everything under the row where the words
> "Title for Month" is found, anyway here it is fixed!
>
> Sub DeleteData()
> Dim sh As Worksheet
> TargetCol = "A"
> Application.ScreenUpdating = False
> For Each sh In Sheets
> sh.Select
> 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
> Application.ScreenUpdating = True
> End Sub
>
>
> --
> Breakfast Guy
> ------------------------------------------------------------------------
> Breakfast Guy's Profile: http://www.thecodecage.com/forumz/member.php?userid=5
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=7255
>
>

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

"Don Guillett" wrote:

> 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
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Yossy" <(E-Mail Removed)> wrote in message
> news:78A4F63A-97FD-4D00-80FA-(E-Mail Removed)...
> > 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
> >
>
>

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
>

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

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
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Yossy" <(E-Mail Removed)> wrote in message
news:33777BF7-F916-4B7C-8DB0-(E-Mail Removed)...
>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
>
> "Don Guillett" wrote:
>
>> 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
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Yossy" <(E-Mail Removed)> wrote in message
>> news:78A4F63A-97FD-4D00-80FA-(E-Mail Removed)...
>> > 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
>> >
>>
>>

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Yossy" <(E-Mail Removed)> wrote in message
news:33777BF7-F916-4B7C-8DB0-(E-Mail Removed)...
>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
>
> "Don Guillett" wrote:
>
>> 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
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Yossy" <(E-Mail Removed)> wrote in message
>> news:78A4F63A-97FD-4D00-80FA-(E-Mail Removed)...
>> > 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
>> >
>>
>>

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




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

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

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