Macro to run in Specified Sheet and Column

[jsd219]

Thank you everyone for the help earlier, i am in need of a little more.


Can anyone help me with this script? Currently it runs, from sheet 1 -
sheet 3. i need it to run in one sheet only "Resources" and i need it
to look in column "N" currently it only looks in Column "A"

Sub DeleteThisStuff()

Dim lngRow As Long
Dim strSearchString As String
Dim lngRowCount As Long
Dim intStartingPosition As Integer
Dim intEndingPosition As Integer
Dim lngStartingRow As Long
Dim lngEndingRow As Long
Dim blnStartFound As Boolean
Dim blnEndFound As Boolean
Dim rngSearch As Range
Dim intBlankCounter As Integer
Dim lngLastBlankRow As Integer
'Set rngSearch = Me.Cells
Dim rngCurrent As Range
Dim MySheet As Worksheet
blnStartFound = False
blnEndFound = True
intBlankCounter = 0

'MsgBox ActiveWorkbook.Name
For Each MySheet In Worksheets
MySheet.Activate
'If MySheet.Name = "ch9" And lngRow = 196 Then Stop
'Debug.Print MySheet.Name
lngRowCount = MySheet.Rows.Count
For lngRow = 1 To lngRowCount
strSearchString = MySheet.Cells(lngRow, 1).Text
'Debug.Print strSearchString
' MySheet.Cells(lngRow, 1).Select
' Set rngCurrent = MySheet.Range(Cells(lngRow, 1),
Cells(lngRow, 1))
If blnStartFound = False And blnEndFound = True Then
intStartingPosition = InStr(1, strSearchString,
"#NAME?", vbBinaryCompare)
If intStartingPosition <> 0 Then
' yay!!! we found it
lngStartingRow = lngRow
blnStartFound = True
blnEndFound = False
GoTo NextRow
End If
ElseIf blnEndFound = False And blnStartFound = True Then
intEndingPosition = InStr(1, strSearchString,
"Copyright")
If intEndingPosition <> 0 Then
' yay!!!
lngEndingRow = lngRow
blnEndFound = True
Set rngSearch = MySheet.Range(Cells(lngStartingRow,
1), Cells(lngEndingRow, 1))
rngSearch.Select
rngSearch.Delete
lngStartingRow = 0
lngEndingRow = 0
lngRow = 1
blnStartFound = False
GoTo NextRow
End If
End If

If Len(strSearchString) = 0 Then
If lngLastBlankRow = (lngRow - 1) Then intBlankCounter
= intBlankCounter + 1
lngLastBlankRow = lngRow
If intBlankCounter > 5 Then
intBlankCounter = 0
lngRow = 0
GoTo NextSheet
End If
End If
NextRow:
Next lngRow
NextSheet:
intBlankCounter = 0
lngRow = 0
lngLastBlankRow = 0
Next MySheet
End Sub

Any and all help is much appreciated

God bless
jsd219

 

[PCLIVE]

This is not tested due to lack of time.

Sub DeleteThisStuff()

Dim lngRow As Long
Dim strSearchString As String
Dim lngRowCount As Long
Dim intStartingPosition As Integer
Dim intEndingPosition As Integer
Dim lngStartingRow As Long
Dim lngEndingRow As Long
Dim blnStartFound As Boolean
Dim blnEndFound As Boolean
Dim rngSearch As Range
Dim intBlankCounter As Integer
Dim lngLastBlankRow As Integer
'Set rngSearch = Me.Cells
Dim rngCurrent As Range
Dim MySheet As Worksheet
blnStartFound = False
blnEndFound = True
intBlankCounter = 0

'MsgBox ActiveWorkbook.Name


Sheets("Resources").Activate
lngRowCount = ActiveSheet.Rows.Count
For lngRow = 1 To lngRowCount
strSearchString = ActiveSheet.Cells(lngRow, 1).Text
If blnStartFound = False And blnEndFound = True Then
intStartingPosition = InStr(1, strSearchString, "#NAME?",
vbBinaryCompare)
If intStartingPosition <> 0 Then
' yay!!! we found it
lngStartingRow = lngRow
blnStartFound = True
blnEndFound = False
GoTo NextRow
End If
ElseIf blnEndFound = False And blnStartFound = True Then
intEndingPosition = InStr(1, strSearchString, "Copyright")
If intEndingPosition <> 0 Then
' yay!!!
lngEndingRow = lngRow
blnEndFound = True
Set rngSearch = ActiveSheet.Range(Cells(lngStartingRow,
14), Cells(lngEndingRow, 14))
rngSearch.Select
rngSearch.Delete
lngStartingRow = 0
lngEndingRow = 0
lngRow = 1
blnStartFound = False
GoTo NextRow
End If
End If

If Len(strSearchString) = 0 Then
If lngLastBlankRow = (lngRow - 1) Then intBlankCounter =
intBlankCounter + 1
lngLastBlankRow = lngRow
If intBlankCounter > 5 Then
intBlankCounter = 0
lngRow = 0
GoTo NextSheet
End If
End If
NextRow:
Next lngRow
NextSheet:
intBlankCounter = 0
lngRow = 0
lngLastBlankRow = 0
End Sub

 

[jsd219]

Unfortunately it is not doing anything

God bless
jsd219