PDA

View Full Version : [SOLVED] Removing unwanted rows



austenr
12-06-2004, 10:40 AM
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
12-06-2004, 10:50 AM
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
12-06-2004, 12:38 PM
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
12-06-2004, 02:15 PM
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
12-06-2004, 02:26 PM
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
12-06-2004, 02:58 PM
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
12-06-2004, 03:28 PM
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
12-06-2004, 03:42 PM
Hmmmmmm. Seems to work but is caught in an endless loop.

Zack Barresse
12-06-2004, 04:06 PM
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
12-06-2004, 05:14 PM
lastRow = 3

Zack Barresse
12-06-2004, 05:24 PM
Hmmm, shouldn't be stuck in a loop. Is the book in question the one you posted? Which sheet?

austenr
12-06-2004, 05:59 PM
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
12-06-2004, 06:07 PM
Glad you got it solved Austen! :yes

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