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