Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RUNTIME 424 OBJECT REQUIRED???
This is meant to clean up excess rows that come into a file from exported
data. If the cell in column 1 is empty or contains certain words, I need to delete the row. It will run through the loop one time. The second time I get a runtime 424 "Object Required" error. What do I need to do to allow this to run through approx 300 rows? Sub row_clean() Dim i, t As Integer Dim cValue As String ' ********* delete unnecessary rows *********** With ActiveCell last_row = Cells(Rows.Count, .Column).End(xlUp).Row For i = 1 To last_row cValue = Cells(i, .Column).Value If cValue = "" Or cValue = "Report:" Or cValue = "Application:" Or cValue = "User:" Or cValue = "JCN" Then Rows(i).Delete last_row = last_row - 1 'not sure if this works or if I need to use the t variable for temp stowage like below ' this next "If" allows for two or more empty cells in sequence If i 1 Then t = i i = t - 1 End If End If Next i End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
RUNTIME 424 OBJECT REQUIRED???
Why do you need to do it a second time?
When deleting you should go bottom up, so change For i = 1 To last_row to For i = last_row To 1 Step -1 -- HTH RP (remove nothere from the email address if mailing direct) "-JEFF-" wrote in message ... This is meant to clean up excess rows that come into a file from exported data. If the cell in column 1 is empty or contains certain words, I need to delete the row. It will run through the loop one time. The second time I get a runtime 424 "Object Required" error. What do I need to do to allow this to run through approx 300 rows? Sub row_clean() Dim i, t As Integer Dim cValue As String ' ********* delete unnecessary rows *********** With ActiveCell last_row = Cells(Rows.Count, .Column).End(xlUp).Row For i = 1 To last_row cValue = Cells(i, .Column).Value If cValue = "" Or cValue = "Report:" Or cValue = "Application:" Or cValue = "User:" Or cValue = "JCN" Then Rows(i).Delete last_row = last_row - 1 'not sure if this works or if I need to use the t variable for temp stowage like below ' this next "If" allows for two or more empty cells in sequence If i 1 Then t = i i = t - 1 End If End If Next i End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
RUNTIME 424 OBJECT REQUIRED???
Thank you Bob. That is more effecient and I'll go w/ it, but what I meant by
second time is the second time the FOR loop executes. I get the error on the line immediatly following the FOR on the second itteration. -JEFF- "Bob Phillips" wrote: Why do you need to do it a second time? When deleting you should go bottom up, so change For i = 1 To last_row to For i = last_row To 1 Step -1 -- HTH RP (remove nothere from the email address if mailing direct) "-JEFF-" wrote in message ... This is meant to clean up excess rows that come into a file from exported data. If the cell in column 1 is empty or contains certain words, I need to delete the row. It will run through the loop one time. The second time I get a runtime 424 "Object Required" error. What do I need to do to allow this to run through approx 300 rows? Sub row_clean() Dim i, t As Integer Dim cValue As String ' ********* delete unnecessary rows *********** With ActiveCell last_row = Cells(Rows.Count, .Column).End(xlUp).Row For i = 1 To last_row cValue = Cells(i, .Column).Value If cValue = "" Or cValue = "Report:" Or cValue = "Application:" Or cValue = "User:" Or cValue = "JCN" Then Rows(i).Delete last_row = last_row - 1 'not sure if this works or if I need to use the t variable for temp stowage like below ' this next "If" allows for two or more empty cells in sequence If i 1 Then t = i i = t - 1 End If End If Next i End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime error '424': Object Required | Excel Discussion (Misc queries) | |||
"Microsoft Visual Basic runtime error '424' object required". | Excel Worksheet Functions | |||
Runtime error only with Office 97 - advice required | Excel Programming | |||
runtime error ....object required | Excel Programming | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |