Deletion of Data across multiple sheet

[Yossy]

Please I wouldn't know why this code is not working. I want to delete all
contents below "Tile in month for the Period" in Column A across multiple
sheets in my workbook. I get error. I want it to ignore "see attached file"
i.e should not clear the content where it sees "see attached file".

Sub Clearcontent()
Dim targetcol As String
Dim sh As Worksheet
Dim myrow As Long
Dim lastrowtodelete As Long

targetcol = "A"
For Each sh In ActiveWorkbook.Sheets
'If ActiveSheet.Name <> sh.Name Then

With sh
myrow = .Columns(targetcol).Find(What:="*Tile in month for the Period*", _
after:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Row + 1
If Application.Trim(Left(.Cells(myrow, 1), 3)) _
= "See" Then myrow = myrow + 1

lastrowtodelete = .Cells(myrow, targetcol).End(xlDown).Row
.Range(.Cells(myrow, targetcol), .Cells(lastrowtodelete,
targetcol)).ClearContents
End With

'End If
Next
End Sub

All help totally appreciated. Thanks a big bunch.

 

[OssieMac]

Is my assumption correct that your error you refer to is that the line
containing See attached... is being deleted? If so, then following line
suggests there might be spaces in front of "See attached...." and you want to
trim them off so that you can compare See.

If Application.Trim(Left(.Cells(myrow, 1), 3)) _
= "See" Then myrow = myrow + 1

If so then you have an error or logic. The spaces must be trimmed first
before identifying the left three actual characters as follows otherwise the
Left function will return space and Se and when trimmed will be Se.

The following line trims spaces first and then gets the Left 3 characters.

If Left(Trim(.Cells(myrow, 1)), 3) _
= "See" Then myrow = myrow + 1

 

[Yossy]

the code doesn't seem to work at all. Not just "see attached file". All help
totally appreciated. Don't know what am missing

Thanks

 

[OssieMac]

Hi Yossy,

It is impossible to tell where your problem lies without your workbook. Does
the code stop with an error. If so, on what line and what is the error
message?

However, your Find code will error out if it does not find the string. You
cannot assign the row number to a variable if the data is not found. See code
below to handle this. Row number only assigned if the string is found.

Also; place some stops in the code as per the following example. When the
code stops, the VBA editor will open. Just rest your cursor over each of the
variables in turn and they will display their value. You should then be able
to see which variable is wrong and that should give you a clue as to what the
problem is.

After each stop, click the Run button in the VBA editor and it will continue
to the next stop.

You will need to click the Reset button to cancel the run when you establish
where the fault lies.

Feel free to get back again if still can't find the problem.

Sub Clearcontent()
Dim targetcol As String
Dim sh As Worksheet
Dim myrow As Long
Dim lastrowtodelete As Long

Dim objCellToFind As Object

targetcol = "A"
For Each sh In ActiveWorkbook.Sheets
'If ActiveSheet.Name <> sh.Name Then

With sh
Set objCellToFind = .Columns(targetcol). _
Find(What:="*Tile in month for the Period*", _
after:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If objCellToFind Is Nothing Then
MsgBox "Not found in sheet " & sh.Name
'Insert code here to handle not found
'Maybe Exit sub or goto a label
Else
myrow = objCellToFind.Row + 1
End If

Stop

If Left(Trim(.Cells(myrow, 1)), 3) _
= "See" Then myrow = myrow + 1

Stop

lastrowtodelete = .Cells(myrow, targetcol).End(xlDown).Row

Stop

.Range(.Cells(myrow, targetcol), .Cells(lastrowtodelete, _
targetcol)).ClearContents

Stop

End With

'End If
Next
End Sub

 

[Yossy]

Ossie, i get the point "it will error out if it does not find the string" but
can you help me include the skip code or ignore those sheets where it it does
not find the string (Tile in month for the Period), and make the code work.

Sub Clearcontent()
Dim targetcol As String
Dim sh As Worksheet
Dim myrow As Long
Dim lastrowtodelete As Long

targetcol = "A"
For Each sh In ActiveWorkbook.Sheets
'If ActiveSheet.Name <> sh.Name Then

With sh
myrow = .Columns(targetcol).Find(What:="*Tile in month for the Period*", _
after:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Row + 1
If Application.Trim(Left(.Cells(myrow, 1), 3)) _
= "See" Then myrow = myrow + 1

lastrowtodelete = .Cells(myrow, targetcol).End(xlDown).Row
..Range(.Cells(myrow, targetcol), .Cells(lastrowtodelete,
targetcol)).ClearContents
End With

'End If
Next
End Sub

Thanks a big bunch. I really appreciate it.

 

[OssieMac]

Hi again Yossy,

Sorry I was not able to get back to you sooner. Simply replace all your code
with the following code. The label is any name that is not a reserved word
and where the label is placed, you put a colon at the end of it. (You don't
use the colon when telling the code to go to it.)

Sub Clearcontent()
Dim targetcol As String
Dim sh As Worksheet
Dim myrow As Long
Dim lastrowtodelete As Long

Dim objCellToFind As Object

targetcol = "A"
For Each sh In ActiveWorkbook.Sheets
'If ActiveSheet.Name <> sh.Name Then

With sh
Set objCellToFind = .Columns(targetcol). _
Find(What:="*Tile in month for the Period*", _
after:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If objCellToFind Is Nothing Then
'*Tile in month for the Period* not found
'so bypass code down to Next.
GoTo myLabel
Else
myrow = objCellToFind.Row + 1
End If

If Left(Trim(.Cells(myrow, 1)), 3) _
= "See" Then myrow = myrow + 1

lastrowtodelete = .Cells(myrow, targetcol).End(xlDown).Row

.Range(.Cells(myrow, targetcol), .Cells(lastrowtodelete, _
targetcol)).ClearContents

End With

'End If

myLabel:
Next
End Sub