Removing unwanted rows

[austenr]

I have a workbook that I copied and pasted data to from a DOS screen. I want to remove the bottom two lines which my macro will do if you enter the data by hand. The problem seems to be the last row of data which is a blank row.

If you run the macro with the data as pasted, it removes nothing. If you remove the 3rd line that was pasted by doing a Delete>Row, the macro remove the second line leaving the first which is what I want.

What I do not understand is what is wrong with the third line that it will not delete. I am attaching the zip file. Thanks in advance if anyone can solve this.

[austenr]

I found the problem but I am not sure how to go about fixing it. On row 2 cell D2 needs to be deleted and on row 3 cell A1 needs to be deleted and then I can run the Macro which works perfectly. Not sure how to incorporate it into the Macro however.

[Zack Barresse]

Hi Austen,


So, will this always be 3 rows? Trying to follow the flow of information here. Also, there is no need to do a 'r = r - 1' line, you are already looping with a For r loop and stepping backwards 1 step every iteration. Are you wanting it to step everyrow, and when it find a blank row step for 2?

Also, I'd take your routine out of your ThisWorkbook module and put it into a Standard Module.

When I ran your code, it deleted all of the rows. What is the input of information? You just want the first line remaining, right?

[austenr]

Hey Zack,

Yes there will always be three rows. I will always want to delete every second and third row. If there is a simpler way I am all for it.

[Zack Barresse]

If there are only 3 rows, can't you just do ..

Rows("2:3").Delete

??

Or is it multiple sets of data, always containing 3 rows?

[austenr]

There are always sets of three.

xx
xx

xx
xx

xx
xx

Where I want to remove the sexond set of "XX" and the blank row beneath it.

HTH

[Zack Barresse]

Well, maybe this would help ...

Sub deleteRows()
    Dim lastRow As Long, i As Long
    lastRow = ActiveSheet.UsedRange.Rows.Count
    For i = 2 To lastRow Step 1
        Rows(i).delete
        Rows(i).delete
    Next i
End Sub

I know it is deleting rows and starting from the beginning. But in lieu of your 'space' problems, this may help.

[austenr]

Hmmmmmm. Seems to work but is caught in an endless loop.

[Zack Barresse]

If you step through your code, what is your lastRow variable returning? Put your cursor in your routine, press F8 four times, then hover your mouse over lastRow, what does it say?

[austenr]

lastRow = 3

[Zack Barresse]

Hmmm, shouldn't be stuck in a loop. Is the book in question the one you posted? Which sheet?

[austenr]

Many apologies....I was indeed trying it on the wrong book! Got a terrible toothache today!! My mind is not clear, please forgive me!! Works great, like always..Thanks so much!!!

[Zack Barresse]

Glad you got it solved Austen!

And no need to apologize! Lord knows I've done worse!