I am tring to clean up a report that is pulled in a off a propritary program
in my company. The format comes in as all "General" cell format. I have
cleaned up some stuff already but am having a hard time getting the below to
work properly.

I have three varibles I want need to look for (any text, "-" or a number)
and do something based on what is active in a cell. From a specific starting
point in the spreadsheet I need to find the first active cell to the right
that contains a number. from there I want to select all cells between the
starting point and the cell with the number and delete them. I want this to
loop through the whole spreadsheet and should clean up the a garbage brought
in from the other program.

Sub step_Five()

Columns("A:A").Select
Selection.Find(What:="Part", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 1).Select
ActiveCell.Name = "Part5"

If ActiveCell.Text = "" Then
Do
ActiveCell.Offset(0, 1).Select
Loop
End If
If ActiveCell.Text = "-" Then
Do
ActiveCell.Offset(1, 0).Select
Loop
End If
If ActiveCell.Text = 0 Then
Do
ActiveCell.Offset(0,-1).Select
ActiveCell.Name = "Part5a"
Range("Part5:Part5a").Select
Selection.Delete Shift:=xlToLeft

"Delete Names out of cell?"

ActiveCell.Offset(1, 0).Select
Loop
End If
End Sub

Anyone have a better way for this?

Thanks
Peter

One thing that I noticed is that you have open end Do loops. I would put
some restrictions on them with a Do While SomethingHappens, Do Until
ConditionMet, Loop While Count <, etc. Without some limit they will either
run endlessly or error out.

"Looping through" wrote:

I am tring to clean up a report that is pulled in a off a propritary program
in my company. The format comes in as all "General" cell format. I have
cleaned up some stuff already but am having a hard time getting the below to
work properly.

I have three varibles I want need to look for (any text, "-" or a number)
and do something based on what is active in a cell. From a specific starting
point in the spreadsheet I need to find the first active cell to the right
that contains a number. from there I want to select all cells between the
starting point and the cell with the number and delete them. I want this to
loop through the whole spreadsheet and should clean up the a garbage brought
in from the other program.

Sub step_Five()

Columns("A:A").Select
Selection.Find(What:="Part", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 1).Select
ActiveCell.Name = "Part5"

If ActiveCell.Text = "" Then
Do
ActiveCell.Offset(0, 1).Select
Loop
End If
If ActiveCell.Text = "-" Then
Do
ActiveCell.Offset(1, 0).Select
Loop
End If
If ActiveCell.Text = 0 Then
Do
ActiveCell.Offset(0,-1).Select
ActiveCell.Name = "Part5a"
Range("Part5:Part5a").Select
Selection.Delete Shift:=xlToLeft

"Delete Names out of cell?"

ActiveCell.Offset(1, 0).Select
Loop
End If
End Sub

Anyone have a better way for this?

Thanks
Peter

I think the problem is with the defintion of findsheet. Interger limit is
1/2 65,536 because integers they are both positive and negative. I think we
need to make it a long as shown below.

You aren't being a pain. it was my fault for not fully testing the code
uder every condition.

from
Function Findsheet(StrDate) As Integer
to
Function Findsheet(StrDate) As Long


"Looping through" wrote:

I am tring to clean up a report that is pulled in a off a propritary program
in my company. The format comes in as all "General" cell format. I have
cleaned up some stuff already but am having a hard time getting the below to
work properly.

I have three varibles I want need to look for (any text, "-" or a number)
and do something based on what is active in a cell. From a specific starting
point in the spreadsheet I need to find the first active cell to the right
that contains a number. from there I want to select all cells between the
starting point and the cell with the number and delete them. I want this to
loop through the whole spreadsheet and should clean up the a garbage brought
in from the other program.

Sub step_Five()

Columns("A:A").Select
Selection.Find(What:="Part", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 1).Select
ActiveCell.Name = "Part5"

If ActiveCell.Text = "" Then
Do
ActiveCell.Offset(0, 1).Select
Loop
End If
If ActiveCell.Text = "-" Then
Do
ActiveCell.Offset(1, 0).Select
Loop
End If
If ActiveCell.Text = 0 Then
Do
ActiveCell.Offset(0,-1).Select
ActiveCell.Name = "Part5a"
Range("Part5:Part5a").Select
Selection.Delete Shift:=xlToLeft

"Delete Names out of cell?"

ActiveCell.Offset(1, 0).Select
Loop
End If
End Sub

Anyone have a better way for this?

Thanks
Peter

Hi Peter

The code below should be one way of doing what you are after (i think
anyway) if not it should give you a good idea of another way to do it.

Option Explicit
Dim LastRow As Integer
Dim MyRng As Range
Dim FindCell As Range
Dim FirstCell As Range
Dim SecondCell As Range

Function StepFive()

On Error Resume Next 'catch error if nothing found

Set MyRng = Range("A1", [A65535].End(xlUp))

Set FindCell = MyRng.Find(What:="Part", LookAt:=xlPart)

If FindCell < "" Then

Set FirstCell = FindCell.Offset(2, 1)

Select Case FindCell.Offset(2, 1).Value

Case ""
Set SecondCell = FirstCell.Offset(0, 1)
Case "-"
Set SecondCell = FirstCell.Offset(1, 0)
Case Is = 0
Set SecondCell = FirstCell.Offset(0, -1)

End Select

Range(FirstCell, SecondCell).Select
Selection.Delete Shift:=xlToLeft

End If

End Function

hope this helps

Steve

I responded to the wrong question.
try this solution

Sub step_Five()

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To Lastrow
cellsData = Cells(Rows.Count, "A")
If InStr(UCase(celldata), "PART") 0 Then
Range("A" & RowCount) = "Part5"
End If

Set nonblankcell = Range("A" & RowCount).End(xlToRight)
If nonblankcell.Column < Columns.Count Then
If IsNumeric(nonblankcell) And _
(nonblankcell.Column < 2) Then

Range(Cells(RowCount, "B"), Cells(RowCount, nonblankcell.Column -
1)).Delete shift:=xlToLeft
End If
End If
Next RowCount
End Sub



"Looping through" wrote:

I am tring to clean up a report that is pulled in a off a propritary program
in my company. The format comes in as all "General" cell format. I have
cleaned up some stuff already but am having a hard time getting the below to
work properly.

I have three varibles I want need to look for (any text, "-" or a number)
and do something based on what is active in a cell. From a specific starting
point in the spreadsheet I need to find the first active cell to the right
that contains a number. from there I want to select all cells between the
starting point and the cell with the number and delete them. I want this to
loop through the whole spreadsheet and should clean up the a garbage brought
in from the other program.

Sub step_Five()

Columns("A:A").Select
Selection.Find(What:="Part", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 1).Select
ActiveCell.Name = "Part5"

If ActiveCell.Text = "" Then
Do
ActiveCell.Offset(0, 1).Select
Loop
End If
If ActiveCell.Text = "-" Then
Do
ActiveCell.Offset(1, 0).Select
Loop
End If
If ActiveCell.Text = 0 Then
Do
ActiveCell.Offset(0,-1).Select
ActiveCell.Name = "Part5a"
Range("Part5:Part5a").Select
Selection.Delete Shift:=xlToLeft

"Delete Names out of cell?"

ActiveCell.Offset(1, 0).Select
Loop
End If
End Sub

Anyone have a better way for this?

Thanks
Peter

Joel, I get a run time error 438 on line "Range(Cells(RowCount, "B"),
Cells(RowCount, nonblankcell.Column1)).Delete shift:=xlToLeft" when I try I
use this code?

Peter

"Joel" wrote:

I responded to the wrong question.
try this solution

Sub step_Five()

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To Lastrow
cellsData = Cells(Rows.Count, "A")
If InStr(UCase(celldata), "PART") 0 Then
Range("A" & RowCount) = "Part5"
End If

Set nonblankcell = Range("A" & RowCount).End(xlToRight)
If nonblankcell.Column < Columns.Count Then
If IsNumeric(nonblankcell) And _
(nonblankcell.Column < 2) Then

Range(Cells(RowCount, "B"), Cells(RowCount, nonblankcell.Column -
1)).Delete shift:=xlToLeft
End If
End If
Next RowCount
End Sub



"Looping through" wrote:

I am tring to clean up a report that is pulled in a off a propritary program
in my company. The format comes in as all "General" cell format. I have
cleaned up some stuff already but am having a hard time getting the below to
work properly.

I have three varibles I want need to look for (any text, "-" or a number)
and do something based on what is active in a cell. From a specific starting
point in the spreadsheet I need to find the first active cell to the right
that contains a number. from there I want to select all cells between the
starting point and the cell with the number and delete them. I want this to
loop through the whole spreadsheet and should clean up the a garbage brought
in from the other program.

Sub step_Five()

Columns("A:A").Select
Selection.Find(What:="Part", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 1).Select
ActiveCell.Name = "Part5"

If ActiveCell.Text = "" Then
Do
ActiveCell.Offset(0, 1).Select
Loop
End If
If ActiveCell.Text = "-" Then
Do
ActiveCell.Offset(1, 0).Select
Loop
End If
If ActiveCell.Text = 0 Then
Do
ActiveCell.Offset(0,-1).Select
ActiveCell.Name = "Part5a"
Range("Part5:Part5a").Select
Selection.Delete Shift:=xlToLeft

"Delete Names out of cell?"

ActiveCell.Offset(1, 0).Select
Loop
End If
End Sub

Anyone have a better way for this?

Thanks
Peter

Steve,

this code technically does work, but it does not do what I really need.
after finding "Part" and offsetting the active cell down 2 and over 1 to
establish the true starting point of evaluation. I need to evaluate if that
active cell contains a number, text, or "-".

If it contains any text I want to keep looking to the right until I find a
number. Once it finds a number I I want to delete all the cells between my
starting point and the first cell that contains a numer. this could be one
cell or 100 cells depending on the info pulled in. from there I need move to
the next row and evaluate the contents of that cell again and continue.

If the next cell contains "-", I just want to skip cell and move to the next
row.

Does this help clariy.
thanks
Peter

"Incidental" wrote:

Hi Peter

The code below should be one way of doing what you are after (i think
anyway) if not it should give you a good idea of another way to do it.

Option Explicit
Dim LastRow As Integer
Dim MyRng As Range
Dim FindCell As Range
Dim FirstCell As Range
Dim SecondCell As Range

Function StepFive()

On Error Resume Next 'catch error if nothing found

Set MyRng = Range("A1", [A65535].End(xlUp))

Set FindCell = MyRng.Find(What:="Part", LookAt:=xlPart)

If FindCell < "" Then

Set FirstCell = FindCell.Offset(2, 1)

Select Case FindCell.Offset(2, 1).Value

Case ""
Set SecondCell = FirstCell.Offset(0, 1)
Case "-"
Set SecondCell = FirstCell.Offset(1, 0)
Case Is = 0
Set SecondCell = FirstCell.Offset(0, -1)

End Select

Range(FirstCell, SecondCell).Select
Selection.Delete Shift:=xlToLeft

End If

End Function

hope this helps

Steve

The line wrapping on the website changed the code . You are missing the
minus 1
Range(Cells(RowCount, "B"), _
Cells(RowCount, nonblankcell.Column - 1).Delete shift:=xlToLeft


"Looping through" wrote:

Joel, I get a run time error 438 on line "Range(Cells(RowCount, "B"),
Cells(RowCount, nonblankcell.Column1)).Delete shift:=xlToLeft" when I try I
use this code?

Peter

"Joel" wrote:

I responded to the wrong question.
try this solution

Sub step_Five()

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To Lastrow
cellsData = Cells(Rows.Count, "A")
If InStr(UCase(celldata), "PART") 0 Then
Range("A" & RowCount) = "Part5"
End If

Set nonblankcell = Range("A" & RowCount).End(xlToRight)
If nonblankcell.Column < Columns.Count Then
If IsNumeric(nonblankcell) And _
(nonblankcell.Column < 2) Then

Range(Cells(RowCount, "B"), Cells(RowCount, nonblankcell.Column -
1)).Delete shift:=xlToLeft
End If
End If
Next RowCount
End Sub



"Looping through" wrote:

I am tring to clean up a report that is pulled in a off a propritary program
in my company. The format comes in as all "General" cell format. I have
cleaned up some stuff already but am having a hard time getting the below to
work properly.

I have three varibles I want need to look for (any text, "-" or a number)
and do something based on what is active in a cell. From a specific starting
point in the spreadsheet I need to find the first active cell to the right
that contains a number. from there I want to select all cells between the
starting point and the cell with the number and delete them. I want this to
loop through the whole spreadsheet and should clean up the a garbage brought
in from the other program.

Sub step_Five()

Columns("A:A").Select
Selection.Find(What:="Part", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 1).Select
ActiveCell.Name = "Part5"

If ActiveCell.Text = "" Then
Do
ActiveCell.Offset(0, 1).Select
Loop
End If
If ActiveCell.Text = "-" Then
Do
ActiveCell.Offset(1, 0).Select
Loop
End If
If ActiveCell.Text = 0 Then
Do
ActiveCell.Offset(0,-1).Select
ActiveCell.Name = "Part5a"
Range("Part5:Part5a").Select
Selection.Delete Shift:=xlToLeft

"Delete Names out of cell?"

ActiveCell.Offset(1, 0).Select
Loop
End If
End Sub

Anyone have a better way for this?

Thanks
Peter

Hi there

First off sorry for the late reply but i had not checked my posts all
weekend. I'm not sure i have got exactly what you want but it should
be workable to get you sorted. It will check the values as with the
previous code but if it finds a text value it will then begin a loop
offsetting one cell to the right until it finds a numerical value then
it will set the range and delete it. You may have to call this code
from a for each next sort of loop if you want it to run every time it
finds the set value but it should give you an idea of a way around
your problem i hope.

Option Explicit
Dim LastRow As Integer
Dim MyRng As Range
Dim FindCell As Range
Dim FirstCell As Range
Dim SecondCell As Range
Dim i As Integer '########Added variable
Dim LastCol As Integer '######## Added variable

Function StepFive()

On Error Resume Next 'catch error if nothing found

Set MyRng = Range("A1", [A65535].End(xlUp))

Set FindCell = MyRng.Find(What:="Part", LookAt:=xlPart)

If FindCell < "" Then

Set FirstCell = FindCell.Offset(2, 1)

Select Case FindCell.Offset(2, 1).Value

Case "-"
Set SecondCell = FirstCell.Offset(1, 0)

Case Is = 0
Set SecondCell = FirstCell.Offset(0, -1)

Case Is < "" 'Changed to look for a text value
'and moved to the end select case
'statement to check the other
'"case" values first
'The line below will check which column holds
'the last value to limit the loop size
LastCol = FirstCell.End(xlToRight).Column

For i = 1 To LastCol 'Begin a loop
'The line below will check for a numerical
'value in the offset cell
If IsNumeric(FirstCell.Offset(0, i)) Then

Set SecondCell = FirstCell.Offset(0, i)
'Call the deletedata function from here
'for this "case" type so you can exit
'the loop to prevent further checking
'of the row
DeleteData

Exit Function

End If

Next i 'Iterate the loop

End Select
'Call the deletedata function here for the other
'"case" types
DeleteData

End If

End Function
Function DeleteData()
'This is just the same code to delete the
'data from the sheet but it has been moved
'into it's own function so you can exit the
'loop with out any problems
Range(FirstCell, SecondCell).Select
Selection.Delete Shift:=xlToLeft
End Function


I hope this helps you out if not post back and i will see what i can
do

Steve

I do not recommend using the select method and moving around cells using the
offset!!!!!!!!!!! this type of coding is difficult to follow and is prone to
errors.

Code should be written to be clear and and easily documented. The change
Looping through recommended is "BAD Program Style". I have a Master's in
computerr Science and have sat through plenty of lectures where teachers
scold students like Looping Through. Looping through deserves a "C" grade.

Using offsets is "sometimes" acceptable when you are writing code that
depends on the selected cell when the code is started.

"Incidental" wrote:

Hi there

First off sorry for the late reply but i had not checked my posts all
weekend. I'm not sure i have got exactly what you want but it should
be workable to get you sorted. It will check the values as with the
previous code but if it finds a text value it will then begin a loop
offsetting one cell to the right until it finds a numerical value then
it will set the range and delete it. You may have to call this code
from a for each next sort of loop if you want it to run every time it
finds the set value but it should give you an idea of a way around
your problem i hope.

Option Explicit
Dim LastRow As Integer
Dim MyRng As Range
Dim FindCell As Range
Dim FirstCell As Range
Dim SecondCell As Range
Dim i As Integer '########Added variable
Dim LastCol As Integer '######## Added variable

Function StepFive()

On Error Resume Next 'catch error if nothing found

Set MyRng = Range("A1", [A65535].End(xlUp))

Set FindCell = MyRng.Find(What:="Part", LookAt:=xlPart)

If FindCell < "" Then

Set FirstCell = FindCell.Offset(2, 1)

Select Case FindCell.Offset(2, 1).Value

Case "-"
Set SecondCell = FirstCell.Offset(1, 0)

Case Is = 0
Set SecondCell = FirstCell.Offset(0, -1)

Case Is < "" 'Changed to look for a text value
'and moved to the end select case
'statement to check the other
'"case" values first
'The line below will check which column holds
'the last value to limit the loop size
LastCol = FirstCell.End(xlToRight).Column

For i = 1 To LastCol 'Begin a loop
'The line below will check for a numerical
'value in the offset cell
If IsNumeric(FirstCell.Offset(0, i)) Then

Set SecondCell = FirstCell.Offset(0, i)
'Call the deletedata function from here
'for this "case" type so you can exit
'the loop to prevent further checking
'of the row
DeleteData

Exit Function

End If

Next i 'Iterate the loop

End Select
'Call the deletedata function here for the other
'"case" types
DeleteData

End If

End Function
Function DeleteData()
'This is just the same code to delete the
'data from the sheet but it has been moved
'into it's own function so you can exit the
'loop with out any problems
Range(FirstCell, SecondCell).Select
Selection.Delete Shift:=xlToLeft
End Function


I hope this helps you out if not post back and i will see what i can
do

Steve