Deleting in Excel 2

[Trudye]

Hi Guys, I am trying to delete all the rows preceeding the row that has 'o1cr' in the first 4 bytes of col 'A'. However it seems to be deleting every other row (all even number rows). The first row is a hdr row and as you can see I skip that one by ck'ing for the literal "AccountNumber".

Any ideas what the heck I'm doing wrong??

Thanx much
Trudye

Function CleanUp_File()
Dim i As Integer
Dim MaxElements As Integer
Dim cnt As Integer
Dim LeftValue As String
LeftValue = ""
cnt = 0
MaxElements = 7000
ActiveCell.Range("A2").Select

For i = 1 To MaxElements
    LeftValue = Left(Cells(i, 1), 11)
    If Left(Cells(i, 1), 4) = "01CR" Then
        Exit For
    ElseIf Cells(i, 1) = "AccountNumber" Then
    Else
           Rows(i).Delete
    End If
Next i
    
End Function

 

[mharroff]

What is happening is you look at the 2nd row and see that is is not "O1CR" and delete it. Each remaining row moves up then you look at the 3rd row (which was the fourth row) thereby skipping every other row.

Maybe you should start at the bottom and move up to find "O1CR" then delete all rows above that.

Dim i As Integer
Dim MaxElements As Integer
Dim cnt As Integer
Dim LeftValue As String
Dim booFound As Boolean

LeftValue = ""
cnt = 0
MaxElements = 15
ActiveCell.Range("A2").Select
booFound = False

For i = MaxElements To 1 Step -1
    LeftValue = Left(Cells(i, 1), 11)
    If booFound = False Then
      If Left(Cells(i, 1), 4) = "01CR" Then
      booFound = True
      End If
    ElseIf Cells(i, 1) = "AccountNumber" Then
    Else
           Rows(i).Delete
    End If
Next i
    
End Sub

 

[KenWright]

No maybe about it - You should ALWAYS start at the bottom and work up when deleting rows.

That having been said, would I be wrong in assuming that you are effectively deleting any row thatd oes not contain 01CR, becasue if so then why not just use Data / Filter / Autofilter and filter on rows containg 01CR and then use Edit / Go To / Special / Visible cells only and then Edit / Delete / Entire row.

If you need it in a macro then it is still more efficient to put the filter route into a macro and do it that way rather than looping.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Trudye]

Thank you sooo much mharroff and Ken for responding so quickly.

However, I cannot start from the bottom. I am trying to eliminate all records that preceed the first 01CR rec.


Be well,
Trudye

 

[Trudye]

There must be a way to delete recs starting from the top.

 

[AnotherHiggins]

First, you can code to loop through and delete from the top down. You just need the logic to work out so you actually move down when something is deleted.

[attn]BUT[/attn]

In your case, looping is unnecessarily slow. Just use the FIND feature starting from the top to find the first entry containing "O1CR".

This should get you started:

Sub Macro1()
Columns("A:A").Select
myRowNum = Selection.Find(What:="O1CR", After:=ActiveCell, _
    LookIn:=xlValues, LookAt:=xlPart).Row - 1
Range(Range("a2"), Range("a" & myRowNum)).EntireRow.Delete
End Sub

That will delete rows 2 through the row before the first occurrence of "O1CR". This is assuming that row 1 is a header row that should not be deleted.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[mharroff]

My solution does delete lines above the last occurence of 01CR. If 01CR occurs on more than one line you will need a different solution.

I start by setting booFound=False. Then beginning at the bottom of your spreadsheet we start looking at each line. If equal to 01CR then booFound is set to true. If not 01CR we look at next line. Once 01Cr is found we start deleting lines above it.

I did notice in my code I set your maxElements at 15 instead of 7000. Try the code and let me know.

Also, if you have more than one occurence of 01CR let us know so we can offer a different solution.

 

[AnotherHiggins]

mharroff,

See Trudye's post time-stamped 15 Mar 06 10:17:

I am trying to eliminate all records that preceed the [attn]first[/attn] 01CR rec.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Trudye]

Thanks John for picking up on the mention of the 'first' rec part.

What should myRowNum be Set to?
I'm getting the msg "object variable or with varible not set."

Thanks also to maharroff for responding.

 

[AnotherHiggins]

To answer your question:

As you can see in the code, the variable myRowNum is equal to
[blue]Selection.Find(What:="O1CR", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart).Row - 1[/blue]

which basically finds the first occurrence of 'o1cr', then takes that cell's row, minus 1.

So if the first occurrence of 'o1cr' is on row 6, myRowNum will be set (automatically by the code) to 5.

So you shouldn't have to do anything other than copy the code in my previous post into a module and run it. Are you joining it with any other code? If so, please post that as well.

And finally the most important question: What line of code is highlighted after you get that error?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Trudye]

Hi John, to answer your last question first, the entire col A is selected.

I pasted your code as is into a function and ran it. No other code involved.

Here is what I ran

Function CleanUp_File()

    Columns("A:A").Select
    myRowNum = Selection.Find(What:="O1CR", After:=ActiveCell, _
        LookIn:=xlValues, LookAt:=xlPart).Row - 1
    Range(Range("a2"), Range("a" & myRowNum)).EntireRow.Delete

End Function

 

[AnotherHiggins]

Trudye,

Ah! I see the problem.

This is not a function! Notice that in the code I posted earlier, it says [blue]Sub[/blue]? That's not the same as [blue]Function[/blue].

Copy the following:

[blue]Sub[/blue] CleanUp_File()
Columns("A:A").Select
myRowNum = Selection.Find(What:="O1CR", After:=ActiveCell, _
    LookIn:=xlValues, LookAt:=xlPart).Row - 1
Range(Range("a2"), Range("a" & myRowNum)).EntireRow.Delete
End Sub

This should be posted into a module. In the VB Editor (where you posted this code into), press [Ctrl]+[R]. That will ensure that the Project Explorer is open. (The Project Explorer looks kind of like the folders in Windows Explorer.)

In the Project Explorer, look for the name of the workbook where you want your macro to 'live'. If this is something you'll use often, it is a good idea to place it in your Personal.xls. The workbook names will look like VBAProject (Personal.xls).

Once you locate the name of the desired workbook, right click on the name > insert > module. This will place the cursor in the big pane on the right. You are now in the module.

Copy 'n' Paste the code above exactly as it is posted.

For more info on what to do with your macro, see faq707-5758, "So where exactly do I put this macro code then??".

Post back with any problems.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Trudye]

I must be doing something wrong I changd the procedure from a Function to a Sub rtn. I pasted your code into the Sheet1 module, and I got the same error.

Question: Why would code work in a Sub and not a function? I have heard of a sub having to be changed to a function if you are planning on passing values. But have never heard of the reverse.

Thanx
Trudye

 

[AnotherHiggins]

While this code should run after being pasted into Sheet1, I explicitly suggested you NOT do that.

Notice how Sheet1 is listed in the Project Explorer under Microsoft Excel Objects? If you had followed the directions in my last post, you would see that Module1 is listed separately under Modules.

But, as I said, it still should have worked even from Sheet1.

Why won't it work for you? I'm sorry, but you have provided zero details about what problems you are having or what line of code (not column in Excel) might be producing errors.

If we don't know what problems you're running into, how can we help overcome those problems?

Please copy the code (in its entirety - you can use [Ctrl]+[A] to select all) and paste back here with a description of what happens when you try to run it. If you are getting an error, please post exactly what the error says, and then take not of what line of code the in VBEditor is highlighted - that is the line of code that produced whatever error you got.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[KenWright]

Code works in both, but they do different things. A function can only return a value to the cell the function is actually in, it cannot affect the contents of another cell (or the formatting), whereas code in a sub routine can do pretty much anything to anything.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Here is a much more detailed explanation:-

http://cpearson.com/excel/differen.htm

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Trudye]

The first time I ran it I ran it from a Module (Module1). The only problem was I put it in a Function. Your response led me to believe that I needed to remove it from Module1 and place it in the worksheet that it was pertaining to.

The problem I'm having is the same problem I stated when I first ran your code. I'm getting the error msg "object variable or with variable not set."
Thus the reason I asked what myRowNum should be set to. When I get that msg in Access VBA it usually denotes that I have failed to set or instantiate an object. I assumed it meant the same thing in Excel VBA.

The line of code that it stops on is:
myRowNum = Selection.Find(What:="O1CR", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart).Row - 1

 

[AnotherHiggins]

What version of Excel are you on? I'm testing on Excel 2000. If you have 97 or before, it might not like some of this syntax.

Try this (just breaking up the steps a little more):

Sub CleanUp_File1()
Columns("A:A").Select
Selection.Find(What:="O1CR", After:=ActiveCell, _
    LookIn:=xlValues, LookAt:=xlPart).Activate
myRowNum = ActiveCell.Row - 1
Range(Range("a2"), Range("a" & myRowNum)).EntireRow.Delete
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Trudye]

I am using Excel 2003. Is there maybe a reference library I'm missing? That is the only thing that I can think of that would allow this code to run on your machine and not on mine. The only other thing that comes to mind is a global definition defined somewhere in scope.

 

[Trudye]

I created a macro that deleted the lines I wanted, it looked a lot like John's macro. It ran ok with no errors.

But it used absolute references rather than relative referecnes. for instance:

    Rows("2:4380").Select
    Range("A4380").Activate
    Selection.Delete Shift:=xlUp

I have managed to find the first 01CR rec. However I do not know how to assign that row as the last row and start the delete (xlUp) from there.

Can anyone help me with that?

Thanks for hanging in there with me,
Trudye